multiple rows into single row - PIVOT?

Question

multiple rows into single row - PIVOT?

Dennis Miller-441243

SSC Rookie

Points: 31
More actions
November 7, 2011 at 7:32 am

#390782

Hi
Apologies if in wrong forum!
I have a table which looks like this...
enquiry_refno attrib_code sub_attrib_code date_value
123456 H018 PRE NULL
123456 H019 JC NULL
123456 H021 NULL 23/10/2010
345678 H018 POST NULL
345678 H019 HS NULL
And I need to be able to get it to look like this so I can report on it...
Enquiry_refno AC_H018 AC_H019 AC_H021 AC_H022
123456 PRE JC 23/10/2010 NULL
345678 POST HS NULL NULL
I have spent all morning trying to create a pivot table so that i can use it in a report. Am I going the wrong way? I have created a view for my initial source (to filter the data down) and then am trying to create a query based on that view. Have been using a variety of scripts to try and assist and am getting nowhere!
Any help would be very appreciated!
Thanks in advance
Dennis Miller

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Because of the variable column names, you'll likely need a bit of dynamic SQL. The link below provides and example of how to do that. The data in the article is numeric in nature and uses SUM. For character based data, you would use MAX instead of SUM.

http://www.sqlservercentral.com/articles/Crosstab/65048/

If you'd like a coded example for the exact data you've posted, take a look at the first article in my signature line below for how to quickly turn your data into "readily consumable" data. Most folks will respond very quickly if they don't have to take the time to create a test table to solve the problem for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dennis Miller-441243 SSC Rookie Points: 31 More actions · Answer 2

Hi Jeff

Thanks for your pointers.

I have managed to get somewhere using the following...

Creating the original view...

SELECT dbo.hl_enquiry.enquiry_refno, dbo.hl_attribute.attrib_code, dbo.hl_attribute.sub_attrib_code, dbo.hl_attribute.date_value

FROM dbo.hl_attribute RIGHT OUTER JOIN

dbo.hl_enquiry ON dbo.hl_attribute.enquiry_refno = dbo.hl_enquiry.enquiry_refno

WHERE (dbo.hl_enquiry.enquiry_status = 0) AND (dbo.hl_attribute.attrib_code = 'H018' OR

dbo.hl_attribute.attrib_code = 'H019' OR

dbo.hl_attribute.attrib_code = 'H021' OR

dbo.hl_attribute.attrib_code = 'H022')

Doing the pivot bit...

select *

from dbo.HAENQ_SuppAttrib_PivotSource as source

pivot

(

max(sub_attrib_code)

for [attrib_code] in ([H018],[H019],[H021],[H022])

) as pvt

order by enquiry_refno desc

which gives me a result set looking like this...

enquiry_refnodate_valueH018H019H021H022

31403NULLPRENULLNULLNULL

31371NULLCURRNULLNULLNULL

313712011-10-25 00:00:00.000NULLNULL

31358NULLPRENULLNULLNULL

31351NULLPRENULLNULLNULL

31264NULLPRENULLNULLNULL

31252NULLPRENULLNULLNULL

31241NULLCURRHSNULLNULL

312412011-10-03 00:00:00.000NULLNULLNULL

31238NULLPRENULLNULLNULL

31237NULLPRENULLNULLNULL

31234NULLCURRNULLNULLNULL

31232NULLPRENULLNULLNULL

31231NULLCURRJCNULLNULL

312312011-09-13 00:00:00.000NULLNULLNULL

31229NULLPRENULLNULLNULL

so I just need to get the date value into either the H021 or H022 column. Do I do this in the query or the original view?

thanks

den

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 3

I think what Jeff expects is something like:

CREATE TABLE #sample_table (

enquiry_refno INT

, attrib_code VARCHAR(10)

, sub_attrib_code VARCHAR(10)

, date_value DATE

)

GO

INSERT INTO [#sample_table] ([enquiry_refno] ,[attrib_code] , [sub_attrib_code] ,[date_value]) select123456, 'H018', 'PRE', NULL

INSERT INTO [#sample_table] ([enquiry_refno] ,[attrib_code] , [sub_attrib_code] ,[date_value]) select123456, 'H019', 'JC', NULL

INSERT INTO [#sample_table] ([enquiry_refno] ,[attrib_code] , [sub_attrib_code] ,[date_value]) select123456, 'H021', NULL, '10/23/2010'

INSERT INTO [#sample_table] ([enquiry_refno] ,[attrib_code] , [sub_attrib_code] ,[date_value]) select345678, 'H018', 'POST', NULL

INSERT INTO [#sample_table] ([enquiry_refno] ,[attrib_code] , [sub_attrib_code] ,[date_value]) select345678, 'H019', 'HS', NULL

GO

SELECT * FROM #sample_table

--------------------------------------------------

-- start actual pivotating

--------------------------------------------------

-- end actual pivotating

--------------------------------------------------

DROP TABLE #sample_table

Dennis Miller-441243 SSC Rookie Points: 31 More actions · Answer 4

Thanks for help but think I have solved it in SSRS now! By manipulating the Matrix in a different way, I have managed to get the results I was after.

Thanks to Jeff and Iulian for assistance offered anyway!