Tricky pivot-like transformation & concatenation

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

SQL Server 2005 Forums

Transact-SQL (2005)

Tricky pivot-like transformation & concatenation

Author

Topic

hamble18
Starting Member

12 Posts

Posted - 2010-06-06 : 15:37:02

Hi folks... I wonder if one of you kind people can suggest how I might do the following.

I need to transform some data as follows:

* Create one row per PersonID
* Within each row, there should be a field for each QuestionCode found
* Where there is more than one row for a PersonID/QuestionCode combination, concatenate the 3-character AnswerCode strings together
* AnswerCodes will ALWAYS be exactly 3 characters long
* There may be any number of QuestionCodes; I need to create whatever fields are required.

For example:

PersonID,QuestionCode,AnswerCode
1,'Q1','01.'
1,'Q1','02.'
1,'Q1','03.'
1,'Q2','01.'
1,'Q4','05.'
1,'Q4','06.'
2,'Q1','02.'
2,'Q3','01.'
2,'Q9','01.'

Into this:
PersonID, Q1, Q2, Q3, Q4, Q9
1, '01.02.03.', '01.', NULL, '05.06.', NULL
2, '02.', NULL, '01.', NULL, '01.'

Any pointers gratefully received.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-06-06 : 22:30:02

select * from
(
select personid, questioncode, stuff((select '.'+cast(answercode as varchar) from 'yours table' b where a.personid=b.personid and a.questioncode=b.questioncode for xml path('')),1,1,'')answercode
from 'yours table' a
group by personid, questioncode) as data
pivot
(max(answercode) for question in ([Q1],[Q2],[Q3],[Q4])) as pvt

Hope can help...but advise to wait pros with confirmation...

hamble18
Starting Member

12 Posts

Posted - 2010-06-07 : 03:01:16

Thanks for your reply waterduck, but I can't make this work - and I don't understand it enough to work out what's wrong.

Also, I can't hard-code the fields in the output (e.g. [Q1], [Q2]), as they could be anything and any number of them.

This is the script I ran based on your response:

CREATE TABLE #temp
(
PersonID int,
QuestionCode varchar(10),
AnswerCode char(3)
)

INSERT INTO #temp VALUES (1,'Q1','01.')
INSERT INTO #temp VALUES (1,'Q1','02.')
INSERT INTO #temp VALUES (1,'Q1','03.')
INSERT INTO #temp VALUES (1,'Q2','01.')
INSERT INTO #temp VALUES (1,'Q4','05.')
INSERT INTO #temp VALUES (1,'Q4','06.')
INSERT INTO #temp VALUES (2,'Q1','02.')
INSERT INTO #temp VALUES (2,'Q3','01.')
INSERT INTO #temp VALUES (2,'Q9','01.')

select * from
(
select personid, questioncode, stuff((select '.'+cast(answercode as varchar) from #temp b
where a.personid=b.personid and a.questioncode=b.questioncode
for xml path('')),1,1,'') answercode
from #temp a
group by personid, questioncode) as data
pivot
(max(answercode) for question in ([Q1],[Q2],[Q3],[Q4])) as pvt

DROP TABLE #temp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-07 : 04:07:02

Use dynamic PIVOT
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-06-07 : 04:32:50

i think you better refer madhi solution since yours column are dynamic

Hope can help...but advise to wait pros with confirmation...

Sachin.Nand

2937 Posts

Posted - 2010-06-07 : 05:24:12

[code]
CREATE TABLE #temp
(
PersonID int,
QuestionCode varchar(10),
AnswerCode char(3)
)

INSERT INTO #temp VALUES (1,'Q1','01.')
INSERT INTO #temp VALUES (1,'Q1','02.')
INSERT INTO #temp VALUES (1,'Q1','03.')
INSERT INTO #temp VALUES (1,'Q2','01.')
INSERT INTO #temp VALUES (1,'Q4','05.')
INSERT INTO #temp VALUES (1,'Q4','06.')
INSERT INTO #temp VALUES (2,'Q1','02.')
INSERT INTO #temp VALUES (2,'Q3','01.')
INSERT INTO #temp VALUES (2,'Q9','01.')

Declare @col as varchar(max)=''
Select @col=@col + ',[' + QuestionCode +']' from #temp group by QuestionCode
Declare @sql as varchar(max)=''
Select @sql=
'select * from
(
select personid, questioncode, stuff((select ''.''+cast(answercode as varchar) from #temp b
where a.personid=b.personid and a.questioncode=b.questioncode
for xml path('''')),1,1,'''') answercode
from #temp a
group by personid, questioncode) as data pivot
(max(answercode) for QuestionCode in (' + stuff(@col,1,1,'') + '))as pvt'
Exec(@sql)

DROP TABLE #temp

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

hamble18
Starting Member

12 Posts

Posted - 2010-06-07 : 06:14:53

That's brilliant; thank you.

In SQL 2005 of course, you have to split this to two separate lines: Declare @col as varchar(max)='' - I like that this will be available to me when I upgrade!

In case anyone else is going to copy this, one small amend; my strings already had the '.' as their last character, I didn't need to separate them; so the insertion of the '.' characters can be removed.

Sachin.Nand

2937 Posts

Posted - 2010-06-07 : 06:56:32

quote:
Originally posted by hamble18

That's brilliant; thank you.

You are welcome

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources