April 5, 2013 at 6:26 am
Hi All,
I have a table like this. but its have 5-6k rows.
1AppName
2AppVersion
3Product
4Date1
5Date2
6Date3
7AppName
8AppVersion
9Product
10Date1
11Date2
12Date3
13AppName
14AppVersion
15Product
16Date1
17Date2
18Date3
.
.
.
.
.
and I want result like this. the logic is that after each 6 records we have a complete row. you can also say that column name are associated with records.
AppName AppVersionProductDate1Date2Date3
1 2 3456
7 8 9101112
13 14 15161718
Thanks for your help.
Sneh
April 5, 2013 at 6:42 am
In keeping with the terseness of your post...
PIVOT
:w00t:
April 5, 2013 at 6:46 am
You need to have some way to order the original data.
Here's your data set up so others can use it: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
SELECT id, col
INTO #testEnvironment
FROM (VALUES(1,'AppName'),(2,'AppVersion'),(3,'Product'),
(4,'Date1'),(5,'Date2'),(6,'Date3'),
(7,'AppName'),(8,'AppVersion'),(9,'Product'),
(10,'Date1'),(11,'Date2'),(12,'Date3'),
(13,'AppName'),(14,'AppVersion'),(15,'Product'),
(16,'Date1'),(17,'Date2'),(18,'Date3')
)a(id,col);
Here's how to get your expected results: -
SELECT
MAX(CASE WHEN col = 'AppName' THEN id ELSE NULL END) AS AppName,
MAX(CASE WHEN col = 'AppVersion' THEN id ELSE NULL END) AS AppVersion,
MAX(CASE WHEN col = 'Product' THEN id ELSE NULL END) AS Product,
MAX(CASE WHEN col = 'Date1' THEN id ELSE NULL END) AS Date1,
MAX(CASE WHEN col = 'Date2' THEN id ELSE NULL END) AS Date2,
MAX(CASE WHEN col = 'Date3' THEN id ELSE NULL END) AS Date3
FROM #testEnvironment
GROUP BY (id-1)/6;
Note that because the "id" column is sequential, I've just grouped them into sixes by dividing by 6. You will need to provide more information about your data so that we can see how to achieve the same result for you - possibly a ROW_NUMBER ranking function?
The results that the above produces is: -
AppName AppVersion Product Date1 Date2 Date3
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 4 5 6
7 8 9 10 11 12
13 14 15 16 17 18
April 5, 2013 at 7:28 am
its working fine.
Thanks for your help.
Thanks
Sneh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply