CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Ashish
Ashish

Reputation: 369

Convert Multiple Rows into Multiple Columns

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

Upvotes: 1

Views: 100

Answers (1)

db9dreamer
db9dreamer

Reputation: 1715

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

Upvotes: 1

Related Questions