My Unpivot is working but I lack a Columns to put the names of the columns which were Unpivot. So far, all the data are well unpivot, I would like to add next to it the 'original' columns names.
My query ('ColumnName' is to illustrate my question, yet does nothing):
SELECT 'ColumnName', myData, wename, weblangue, wid FROM website
UNPIVOT
(
myData FOR names IN (sport, yahoo, mobile, di, onet, player)
) temp
I would like to add to each resulted rows the corresponding names: (sport, yahoo, mobile, di, onet, player) I know that i can retrive the columns names with this query :
SELECT C.name FROM sys.columns c WHERE c.object_id = OBJECT_ID('dbo.website')
Instead of trying to retrieve the column names, since every 6 rows it is repeated, I just have to manually add the names for every n rows :
SELECT
CASE
WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =1 THEN 'sport'
WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =2 THEN 'yahoo'
WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =3 THEN 'mobile'
WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =4 THEN 'di'
WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =5 THEN 'onet'
WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =0 THEN 'player'
END AS Label,
isPlayed, wename, webgue, w_id from
website
UNPIVOT
(
isPlayed for names in (sport, yahoo, mobile, di, onet, player)
) temp