SQLTeam.com | Weblogs | Forums

Unpivot with Column Name


#1

hi

I have this set of data:

F3 F4 F5 F6 F7 F8 F9 F10 F11 F12 F13
LT LT HT-S HT-S HT-S HT-L HT-L EHT EHT EHT NULL
230V/400V Total 6.6KV 22KV Total 22KV Total 66KV Total Total Total

How do i transpose or unpivot then to this?

Source Column1 Column2
F3 LT 230V/400V
F4 LT Total
F5 HT-S 6.6KV
F6 HT-S 22KV
F7 HT-S Total
F8 HT-L 22KV
F9 HT-L Total
F10 EHT 66KV
F11 EHT Total
F12 EHT Total
F13 Total

I tried this

SELECT Source, Column1
FROM [dbo].[Staging_KPI_TempHeader]
UNPIVOT
(
Column1
FOR Source IN ([F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12], F13)
) As

But is not working fiine. Thanks alot


#2

do you have another column like ID in your table ?


#3

hi

i don't have any ID column in the table. Thanks


#4

any other column to determine the sequence or ordering of the record in the table ?


#5

Also, you will only have 2 rows in that table ?

; with cte as
(
    select    Source, rn, col
    from    
        (
            select    *, rn = row_number() over (order by [some column])
            from    #tbl 
        ) d
        unpivot
        (
            col
            for Source in ([F3], [F4], [F5], . . .)
        ) up
)
select    *
from    cte
    pivot
    (
        max (col)
        for rn in ([1], [2])
    ) p

#6

hi

Thanks a lot. Finally.