Question about plus rows = columns ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question about plus rows = columns ?

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-04-15 : 05:37:17

Hi all,

I have some problems about plus rows in table
My table:
ID-Question-Answer
1---A--------A1
1---B--------B3
1---C--------C6

2---A--------A123
2---C--------C789
...

One ID may be included 3 question or 2 question,or less ...
I can define some questions,answers before as field:

Question A is Question1 - Answer1
Question B is Question2 - Answer2
Question C is Question3 - Answer3

My expected output :
ID - Question1 - Answer1---Question2--Answer2 ---Question3----Answer3
1-------A --------A1----------B---------B3----------C-----------C6
2-------A---------A123--------Null------Null--------C-----------C789
........Null-------Null------..........

Please help me solve this problem!
Thank you very much.

Sachin.Nand

2937 Posts

Posted - 2010-04-15 : 05:51:10

[code]
select id,
max(case when Question='A' then Question end)as Question1,
max(case when Answer='A1' then Answer end)as Answer1
.
.
.
and so on....
from yourtable
group by ID
[/code]

PBUH

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-15 : 05:53:17

[code]CREATE TABLE #tmp (
ID INT,
Question VARCHAR(1),
Answer VARCHAR(4)
)

INSERT INTO #tmp
SELECT 1, 'A', 'A1'
UNION ALL SELECT 1, 'B', 'B3'
UNION ALL SELECT 1, 'C', 'C6'
UNION ALL SELECT 2, 'A', 'A123'
UNION ALL SELECT 2, 'C', 'C789'
UNION ALL SELECT 3, 'B', 'B7'
UNION ALL SELECT 3, 'C', 'C8'
UNION ALL SELECT 4, 'A', 'A1'
UNION ALL SELECT 4, 'B', 'B2'

SELECT COALESCE(a.ID, b.ID, c.ID),
a.Question AS Question1, a.Answer AS Answer1,
b.Question AS Question2, b.Answer AS Answer2,
c.Question AS Question3, c.Answer AS Answer3
FROM ( SELECT ID, Question, Answer
FROM #tmp
WHERE Question = 'A') a
FULL OUTER JOIN (
SELECT ID, Question, Answer
FROM #tmp
WHERE Question = 'B') b
ON a.ID = b.ID
FULL OUTER JOIN (
SELECT ID, Question, Answer
FROM #tmp
WHERE Question = 'C') c
ON a.ID = c.ID OR b.ID = c.ID
ORDER BY COALESCE(a.ID, b.ID, c.ID)

DROP TABLE #tmp[/code]

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-15 : 05:57:25

OR, using Idera's solution (which looks more efficient)

select id,
max(case when Question='A' then Question  end)as Question1,
max(case when Answer LIKE 'A%' then Answer  end)as Answer1,
max(case when Question='B' then Question  end)as Question2,
max(case when Answer LIKE 'B%' then Answer  end)as Answer2,
max(case when Question='C' then Question  end)as Question3,
max(case when Answer LIKE 'C%' then Answer  end)as Answer3
FROM #tmp
GROUP BY ID

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-04-15 : 10:13:50

Thanks Idera and DBA very much.
In Idera' solution,if Answer changed as '123' or other something,it will be wrong !
In DBA' solution is good but too quite complex for me.If you can change like Idera'solution but it does not depend on answers ! it's very good to me !

Thanks again !

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-15 : 10:20:39

quote:
Originally posted by pamyral_279

Thanks Idera and DBA very much.
In Idera' solution,if Answer changed as '123' or other something,it will be wrong !
In DBA' solution is good but too quite complex for me.If you can change like Idera'solution but it does not depend on answers ! it's very good to me !

Thanks again !

Oh, it's not that complex. Ignore the CREATE TABLE #tmp, etc. That's just creating some test data. The other option would be to use 2 pivot queries, but that would still require a join, and be equally complex.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-04-15 : 11:05:29

Have you ideas about idera solution if answers change !

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-15 : 12:28:59

quote:
Originally posted by pamyral_279

Have you ideas about idera solution if answers change !

Not really. Its simplicity is the reason it doesn't work.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-04-22 : 10:16:52

quote:

SELECT COALESCE(a.ID, b.ID, c.ID),
a.Question AS Question1, a.Answer AS Answer1,
b.Question AS Question2, b.Answer AS Answer2,
c.Question AS Question3, c.Answer AS Answer3
FROM ( SELECT ID, Question, Answer
FROM #tmp
WHERE Question = 'A') a
FULL OUTER JOIN (
SELECT ID, Question, Answer
FROM #tmp
WHERE Question = 'B') b
ON a.ID = b.ID
FULL OUTER JOIN (
SELECT ID, Question, Answer
FROM #tmp
WHERE Question = 'C') c
ON a.ID = c.ID OR b.ID = c.ID
ORDER BY COALESCE(a.ID, b.ID, c.ID)

Hi all,
If i have about 20 questions and 250.000 records in table.Sql still is good in performance ??
Because i think that sql is very long.
Have there way to test about that ?

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources