Pivoting specific fields based on the grouping of another field

Question

Pivoting specific fields based on the grouping of another field

sqlenvy

Valued Member

Points: 59
More actions
April 6, 2010 at 1:32 pm

#222919

Here's an example of the problem I'm trying to solve. I start with the following data in a table:
table code comment
t_a cd_1 com_1
t_a cd_2 com_2
t_a cd_3 com_3
t_b cd_1 com_1
t_b cd_2 com_2
t_b cd_3 com_3
t_c cd_1 com_1
t_c cd_2 com_2
t_c cd_3 com_3
Here's the format I need go get:
table code1 comment1 code2 comment2 code3 comment3
t_a cd_1 com_1 cd_2 com_2 cd_3 com_3
t_b cd_1 com_1 cd_2 com_2 cd_3 com_3
t_b cd_1 com_1 cd_2 com_2 cd_3 com_3
So what I'm trying to do is pivot all the data in the "code" and "comment" fields for each "table". I know I could create a temp table and then use a cursor but I want to find a set-based solution for this problem. I'm thinking I can somehow do this with a recursive cte but I just can't figure out how to do it. Thanks for any assistance you can provide.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Will this help you?

I am writing this code from my home where i have no access to SQL Server. once i reach office i will check the validity of that query.

PLease inform us here if this code helped you..

Cheers,

C'est Pras!!

EDIT: REMOVED MY CRAP CODE 😀

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hi there, this is the working version of the code.. please ignore the code present in the post above

BUT BEFORE U MOVE ONTO THE CODE, I WOULD SUGGEST U READ THIS : http://www.sqlservercentral.com/articles/Best+Practices/61537/

IT IS REALLY HARD TO CREATE A TABLE, CREATE SAMPLE DATA, CREATE CODE OURSELVES JUST FOR THE SAKE OF YOUR CONVENIENCE.

SO IN FUTURE, FOLLOW THE LINK ABOVE TO POST YOUR QUESTION

Now, this will be code for our request:

IF OBJECT_ID('TEMPDB..#PIVOT_TABLE') IS NOT NULL

DROP TABLE #PIVOT_TABLE

CREATE TABLE #PIVOT_TABLE

(

TABLE_NAME VARCHAR(10),

CODE_NAME VARCHAR(10),

COMMENT VARCHAR(10)

)

INSERT INTO #PIVOT_TABLE

SELECT 'tab_a','cod_1', 'comment_1'

UNION ALL

SELECT 'tab_a','cod_2', 'comment_2'

UNION ALL

SELECT 'tab_a','cod_3', 'comment_3'

UNION ALL

SELECT 'tab_b','cod_1', 'comment_1'

UNION ALL

SELECT 'tab_b','cod_2', 'comment_2'

UNION ALL

SELECT 'tab_b','cod_3', 'comment_3'

UNION ALL

SELECT 'tab_c','cod_1', 'comment_1'

UNION ALL

SELECT 'tab_c','cod_2', 'comment_2'

UNION ALL

SELECT 'tab_c','cod_3', 'comment_3'

SELECT TABLE_NAME,CODE_NAME,COMMENT FROM #PIVOT_TABLE

-- CODE

WITH FIRST_PIVOT ([TABLE_NAME], [Cod_1],[Cod_2],[Cod_3])

AS

(

SELECT [TABLE_NAME], [1] ,[2],[3]

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY TABLE_NAME) AS ROW_ID , TABLE_NAME, CODE_NAME

FROM #PIVOT_TABLE) ipt

PIVOT (MAX(CODE_NAME) FOR ROW_ID IN ([1],[2],[3])) pvt

),

SECOND_PIVOT ([TABLE_NAME], [Comment_1],[Comment_2],[Comment_3])

AS

(

SELECT [TABLE_NAME], [1] ,[2],[3]

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY TABLE_NAME) AS ROW_ID , TABLE_NAME, COMMENT

FROM #PIVOT_TABLE) ipt

PIVOT (MAX(COMMENT) FOR ROW_ID IN ([1],[2],[3])) pvt

)

SELECT FP.[TABLE_NAME], FP.[Cod_1],SP.[Comment_1],FP.[Cod_2],SP.[Comment_2],FP.[Cod_3],SP.[Comment_3]

FROM FIRST_PIVOT FP

JOIN SECOND_PIVOT SP

ON FP.[TABLE_NAME] = SP.[TABLE_NAME]

Please let us know here if this code worked for you.

Cheers,

C'est Pras!!

Hariprasad.M SSC Veteran Points: 236 More actions · Answer 3

Check this this will help you out, with out CTE

Create table #Demo

(

cl varchar(10), c2 varchar(10), c3 varchar(10)

)

insert into #Demo values('t_a', 'cd_1', 'com_1')

insert into #Demo values('t_a', 'cd_2', 'com_2')

insert into #Demo values('t_a', 'cd_3', 'com_3')

insert into #Demo values('t_b', 'cd_1', 'com_1')

insert into #Demo values('t_b', 'cd_2', 'com_2')

insert into #Demo values('t_b', 'cd_3', 'com_3')

insert into #Demo values('t_c', 'cd_1', 'com_1')

insert into #Demo values('t_c', 'cd_2', 'com_2')

insert into #Demo values('t_c', 'cd_3', 'com_3')

select cl,

replace(max(convert(varchar, charindex('cd_1', c2)) + '#' + c2), '1#', '') as cd_1,

replace(max(convert(varchar, charindex('cd_2', c2)) + '#' + c2), '1#', '') as cd_2,

replace(max(convert(varchar, charindex('cd_3', c2)) + '#' + c2), '1#', '') as cd_3,

replace(max(convert(varchar, charindex('com_3', c3)) + '#' + c3), '1#', '') as com_3

from #demo

group by cl

Hariprasad.M

sqlenvy Valued Member Points: 59 More actions · Answer 4

Thank You so much for your help. I'll work with the code you've place there for me.

Someone else gave me a reply as well and told I need to submit my request according to some guidelines so I'll try to do better next time.

Thanks again,

Tim

sqlenvy Valued Member Points: 59 More actions · Answer 5

Thanks so much for your help. Sorry about the way I submitted it. I'll read the article and do it correctly going forward. Again, I appreciate your help.

Tim Zepin