Get Row value as Column Header

Question

Get Row value as Column Header

karthik82.vk

SSCrazy

Points: 2002
More actions
December 21, 2014 at 10:08 pm

#293246

Hi,
I have two tables namely lu_parameter and tbl_param_values. The lu_parameter table consists of param_id and parameter column containing id numbers for the parameter names.
UIDParam_ID Parameter Param_Threshold
1P1 Parameter1 NULL
4P2 Parameter2 NULL
6P3 Parameter3 NULL
7P4 Parameter4 NULL
8P5 Parameter5 NULL
9P6 Parameter6 NULL
The tbl_param_values table consists of values corresponding to the parameters with the param_id value as the column header.
UIDSiteIDP1P2P3P4P5P6P7P8
1721177503107NULLNULLNULL
27227129537710NULLNULLNULL
372897125851113NULLNULLNULL
4726881252997NULLNULLNULL
5727867502612NULLNULLNULL
673071312567107NULLNULLNULL
77214127535911NULLNULLNULL
I want to join these two tables so that in the result query instead of param_id value as column heading, I need to have the parameter value as the column heading.
UIDSiteIDParameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6 Parameter7 Parameter8
17211
27227
37289
47268
57278
67307
77214
I have attached the screenshot of the data for reference. I am not that much aware of sql queries. Can anyone please help me to achieve this?
https://www.dropbox.com/s/464c4nc850di1pv/Parameter.png?dl=0]
Thanks for your help..

Viewing 10 posts - 1 through 9 (of 9 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

You can probably do that following the examples in Jeff Moden's article. http://www.sqlservercentral.com/articles/T-SQL/63681/

I would be nice and work on it, but it's almost 2 AM here... start there and if you get stuck, post your create table definition and insert statements to populate the table with dummy data (similar to the real data, but not necessarily real), and then you should get a tested result.

HTH

karthik82.vk SSCrazy Points: 2002 More actions · Answer 2

Hi,

I tried but its not working. Since I have two tables, I am confused on how to pivot them. I have attached the data base scripts as requested by you. The data is already attached in the main forum.

Can you please help me out?

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Post what you tried.

And while you're at it , could you explain how the logic works to generate what you're describing? I have no clue how you got from your two source tables to the final result.

karthik82.vk SSCrazy Points: 2002 More actions · Answer 4

Hi,

Please find my code...

SELECT pivottable.[Parameter 1], pivottable.[Parameter 2], pivottable.[Parameter 3]
from
(
SELECT Param_ID, Parameter
from lu_Parameter
) as st
pivot
(
max(Param_ID)
FOR Parameter in ([Parameter 1], [Parameter 2], [Parameter 3])
) as pivottable

I have managed to get the paramter name in the title and parameter id as row. How to join this pivot table with my tbl_param_data table and get data in the format of the one shown in the attached png image?

Also in this section FOR Parameter in ([Parameter 1], [Parameter 2], [Parameter 3]) i'm passing the values inside in as Parameter 1, Parameter 2, Parameter 3. So i'm predefining the values here. But, these values will not be the same for always. So it has to be dynamic. How to set this as dynamic?

Please help me to complete this...

uravindarreddy Ten Centuries Points: 1390 More actions · Answer 5

Try below code, is it what you want?

DECLARE @HeaderSQL as varchar(4000)

SET @HeaderSQL = (

SELECT

STUFF(

(SELECT ', [' + Param_ID + '] AS [' + Parameter + ']'

FROM [dbo].[lu_parameter]

FOR XML PATH('')) , 1,1, ''

)

DECLARE @PivotSQL as varchar(4000)

SET @PivotSQL = 'SELECT [UID], [SITEID] ,' + @HeaderSQL + 'FROM [dbo].[tbl_param_Data]'

exec (@PivotSQL)

karthik82.vk SSCrazy Points: 2002 More actions · Answer 6

Hi Aravind,

Thanks for your help. I have already Figured this out...

uravindarreddy Ten Centuries Points: 1390 More actions · Answer 7

Hi Karthik,

Oh great!. Can you post your solution (code) here?

karthik82.vk SSCrazy Points: 2002 More actions · Answer 8

Hi Piet,

I have created a dynamic pivot, that will create row headers automatically from the table without predefining them.

My only concern now is joining this pivot table with my tbl_para_data table.

Can you help me out with that?

karthik82.vk SSCrazy Points: 2002 More actions · Answer 9

Hi Aravind,

Please find the code for dynamic header choosing in Pivot Table

DECLARE @cols AS NVARCHAR(MAX),

@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ', ' + Parameter AS [text()]

from lu_Parameter order by Param_ID

FOR XML PATH('')

), 1, 1, '' )

set @query = 'SELECT clientid, studyid, ' + @cols + ' from

(

SELECT clientid, StudyID, Param_ID, Parameter

from lu_Parameter p

) as st

pivot

(

max(Param_ID)

FOR Parameter in (' + @cols +')

) as pvt'

)

select @query