converting rows into columns in sql

Question

How to query to get following result...?!!

table 1:

MainID col1 col2

1       qq    qq

2       qq    qq

3       qq    qq

4       qq    qq

5       qq    qq

table 2

MainID      lineNo      Text

1           1           price

1           2           name

1           3           date

2           1           price

2           2           name

2           3           date

i need a query to result like

MainId     Col1    col2     price   name    date

1           qq      qq      price   name    date

2           qq      qq      price   name    date

This requires 3 different columns to be made on 3 different conditions for a MainID ; forming a single row.

Tony Stark · Accepted Answer · 2014-09-22 12:59:39Z

1

You should be able to do the following by joining on multiple embedded queries:

Select 
      table1.MainID, table1.col1, table1.col2, q1.price, q2.name, q3.date
from 
table1 
left outer join (select 
                 MainID, lineNo, Text as price 
                 from 
                 table2) q1 on table1.MainID = q1.MainID
left outer join (select 
                 MainID, lineNo, Text as name 
                 from table2) q2 on table1.MainID = q2.MainID
left outer join (select 
                 MainID, lineNo, Text as date 
                 from table2) q3 on table1.MainID = q3.MainID

edited Sep 22, 2014 at 12:59

Tony Stark

7616 silver badges22 bronze badges

answered Sep 22, 2014 at 11:07

Mattgb

4283 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-09-22 18:52:38Z

0

This is a pivot query. However, there are other ways to solve it. Here is a method using joins:

select t1.mainid, t1.col1, t1.col2, tprice.text as price, 
       tname.text as name, tdate.text as date
from table1 t1 left join
     table2 tprice
     on t1.mainid = tprice.mainid and t2.lineno = 1 left join
     table2 tname
     on t1.mainid = tname.mainid and t2.lineno = 2 left join
     table2 tdate
     on t1.mainid = tdate.mainid and t2.lineno = 3;

You can also do this with conditional aggregation and using the pivot keyword.

edited Sep 22, 2014 at 18:52

answered Sep 22, 2014 at 11:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

converting rows into columns in sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related