Convert Multiple Rows into Multiple Columns

Question

Guys I need to change Rows data into column i have data like this.

AID QID Answer IsTrue
1   11  A1     1
2   11  A2     0
3   11  A3     0
4   11  A4     0

I like to have output like this.

QID AID1 Answer1 IsTrue1 AID2 Answer2 Istrue2 AID3 Answer3 IsTrue3 AID4 Answer4 IsTrue4
11   1   A1      1       2    A2      0       3    A3      0       4    A4      0

I have tried multiple Pivots and joining data back but we cannot apply min,max sum on Istrue as it is BIT datatype. Do we have something really easy approach to this problem?

Thanks

Yes every question have maximum 4 answers so it will be always till 4. — Ashish
– Ashish, Commented Nov 30, 2013 at 10:40

db9dreamer · Accepted Answer · 2013-11-30 13:31:53Z

This:-

create table #source (
    aid int,
    qid int,
    answer char(2),
    istrue bit
)
insert into #source values
    (1,11,'a1',1),
    (2,11,'a2',0),
    (3,11,'a3',0),
    (4,11,'a4',0),
    (1,12,'a5',0),
    (2,12,'a6',0),
    (3,12,'a7',1),
    (4,12,'a8',0)

select s.qid,
    q1.aid as aid1, q1.answer as answer1, q1.istrue as istrue1,
    q2.aid as aid2, q2.answer as answer2, q2.istrue as istrue2,
    q3.aid as aid3, q3.answer as answer3, q3.istrue as istrue3,
    q4.aid as aid4, q4.answer as answer4, q4.istrue as istrue4
from (
    select distinct qid
    from #source
) s
join #source q1 on q1.qid=s.qid and q1.aid=1
join #source q2 on q2.qid=s.qid and q2.aid=2
join #source q3 on q3.qid=s.qid and q3.aid=3
join #source q4 on q4.qid=s.qid and q4.aid=4
order by s.qid

produces:-

qid aid1 answer1 istrue1 aid2 answer2 istrue2 aid3 answer3 istrue3 aid4 answer4 istrue4
11  1    a1      1       2    a2      0       3    a3      0       4    a4      0
12  1    a5      0       2    a6      0       3    a7      1       4    a8      0

Collectives™ on Stack Overflow

Convert Multiple Rows into Multiple Columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related