How do I write the SQP query (SELECT)

Question

0.00/5 (No votes)

See more:

Hi, I want to create a new table taking data from another one. The source tabla has 3 columns: Order_ID, Meta_Key, Meta_Value.

Meta_Key are different sub_items that are repeated for every Order_ID, which value are defined in the column Meta_Value:

Order_ID / Meta_Key / Meta_Value
1 / product_id / 101
1 / qty / 1
1 / total / 20
2 / product_id / 105
2 / qty / 2
2 / total / 100
3 / product_id / 102
3 / qty / 1
3 / total / 30
...

How would be the SQL query to obtain the new table? with the following columns:

Order_ID / product_id / qty / total
1 / 101 / 1 / 20
2 / 105 / 2 / 100
3 / 102 / 1 / 30

Thank you for your help.
3
2
3

What I have tried:

How to write the SQL query (SELECT) to obtain the new table. See the description.
Thank you.

Posted 30-May-19 10:07am

Member 14476015

Updated 30-May-19 12:54pm

Add a Solution

2 solutions

Solution 1

Try this out

SQL

DECLARE @Tmp TABLE (OrderID INT, Meta_Key varchar(16), Meta_Value INT)
INSERT @Tmp
VALUES (1 ,'product_id', 101)
,     (1 ,'qty', 1)
,     (1 ,'total', 20)
,     (2 ,'product_id', 105)
,     (2 ,'qty', 2)
,     (2 ,'total', 100)
,     (3 ,'product_id', 102)
,     (3 ,'qty', 1)
,     (3 ,'total', 30)

DECLARE @Tmp2 TABLE (OrderID INT, ProductID INT, Qty INT, Total INT)
INSERT @Tmp2 (OrderID) SELECT Distinct OrderID FROM @Tmp

UPDATE	n
SET		n.ProductID = P.Meta_Value
,		n.Qty = Q.Meta_Value
,		n.Total = t.Meta_Value

FROM		@Tmp2  n
INNER JOIN @Tmp  p ON n.OrderID = p.OrderID AND p.Meta_Key = 'product_id'
INNER JOIN @Tmp  q ON n.OrderID = q.OrderID AND q.Meta_Key = 'qty'
INNER JOIN @Tmp  t ON n.OrderID = t.OrderID AND t.Meta_Key = 'Total'

SELECT * FROM @Tmp2

You probably could do this with a PIVOT and subqueries, but this schema is simple enough to do this way

Posted 30-May-19 12:24pm

MadMyche

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

k5054 · Accepted Answer · 2019-05-30T12:54:00

Ick.
This will work if you've only got one product_id/qty/total per order

SQL

SELECT id.Order_ID, product_id, qty, total
FROM  (SELECT Order_ID, Meta_Value AS product_id FROM TheTable WHERE Meta_Key = 'product_id') AS id
JOIN (SELECT Order_ID, Meta_Value AS qty FROM TheTable WHERE Meta_Key = 'qty') AS q  ON (id.Order_ID = q.Order_ID)
JOIN (SELECT Order_ID, Meta_Value AS total FROM TheTable WHERE Meta_Key = 'total') AS t ON (id.Order_ID = t.Order_ID);

If you have more than one product_id/qty/total per order_id then you're in a heap of trouble - SQL doesn't guarantee the order of rows, and most SQL engines will re-order as records get modified.