quote:

---

Originally posted by mchughl1

Hi,

I have a dataset that I need to pivot two columns in.
The column names are dynamic in that new values need to be catered for so I do not think I can use case statements.

The dataset is shown as follows.

Transo Segment Name P D
1000000 SERVICE 1 0.1 72
1000000 SERVICE 2 0.1 72
1000000 NULL 0.2 80
1000000 SERVICE 6 0.2 144
1000000 SERVICE 6 0.2 144
1000000 SERVICE 1 0.1 75
1000000 SERVICE 2 0.1 75

I want to pivot it like so! - some values hidden

Transo S1P S2P S6P NULL S1D S2D S6D NULL
1000000 0.2 0.2 0.4 0.2 147 147 288 80


I am able to pivot one column, just not two using the code as follows.

DECLARE @columns VARCHAR(max)


DECLARE @cols NVARCHAR(MAX)
SELECT @columns =@columns+ STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' +niku.Z_RemoveChars(t2.SEGMENT_NAME)
FROM Z_RPT_LABOUR_FILE_PIVOT AS t2
ORDER BY '],[' + niku.Z_RemoveChars(t2.SEGMENT_NAME)
FOR XML PATH('')
), 1, 2, '') + ']'


print @columns
DECLARE @query VARCHAR(max)

SET @query = '
SELECT transno, '+ @columns + '
FROM Z_RPT_LABOUR_FILE_PIVOT
PIVOT
(
max(DOLLAR)
FOR [SEGMENT_NAME]
IN (' + @columns + ')
)
AS PERC
group by transno, '+ @columns + '
'

EXECUTE(@query)

Any help on how to achieve this will be gratefully appreciated

---