July 10, 2009 at 5:27 am
Hi ,
Can some one help me on generating Tsql for converting Colums in to rows.
Example as below
Table 1
Value
V1
V2
V3
V4
V5
Table 2
ID Value
ID1 V1
ID1 V2
ID1 V3
ID2 V2
ID2 V5
Desired Output
ID V1 V2 V3 V4 V5
ID1 YES YES YES NO NO
ID2 NO YES NO NO YES
July 10, 2009 at 5:40 am
is your maximum column V5 or V9999
July 10, 2009 at 5:49 am
Dave Ballantyne (7/10/2009)
is your maximum column V5 or V9999
Hi dave,
Maximum colum is random and can be up to 100. It depends on the numbers of items being added or deleted.
Thanks
Vips
July 10, 2009 at 6:22 am
hi ,
no solution is particularly clean but this should do you
select table2.id,
coalesce(max(Case when table2.Value = 'V1' then 'Yes' end),'No'),
coalesce(max(Case when table2.Value = 'V2' then 'Yes' end),'No'),
coalesce(max(Case when table2.Value = 'V3' then 'Yes' end),'No'),
coalesce(max(Case when table2.Value = 'V4' then 'Yes' end),'No'),
coalesce(max(Case when table2.Value = 'V5' then 'Yes' end),'No') --etc
from table2
group by table2.id
July 10, 2009 at 6:44 am
Something like this should work. Only drawback is that you have to know what the different possible values are beforehand. If you don't know that, then it's still doable, but you'll need to use a dynamic SQL query to accomplish it. I have a script for that as well, so if you need that I can probably figure something out for you.
DECLARE @Table2 TABLE
(
ID INT,
[Value] VARCHAR(20)
)
INSERT INTO @Table2
SELECT 1, 'V1'
UNION
SELECT 1, 'V2'
UNION
SELECT 1, 'V3'
UNION
SELECT 2, 'V2'
UNION
SELECT 2, 'V5'
SELECT
ID,
[V1] AS [V1],
[V2] AS [V2],
[V3] AS [V3],
[V4] AS [V4],
[V5] AS [V5]
FROM
(
SELECT
ID,
[Value]
FROM @Table2
) RS
PIVOT
(
COUNT([Value])
FOR
[Value] IN ([V1],[V2],[V3],[V4],[V5])
) AS pvt
On an unrelated note - how exactly do you post code in here that looks formatted properly? I mean the script looks fine in my SQL Server Management Studio, but here it looks terrible.
July 19, 2009 at 9:54 pm
Read this to solve the problem of an unknown number of columns.
http://www.sqlservercentral.com/articles/cross+tab/65048/
What I'd like to know is why do you need to denormalize data in such a fashion? Spreadsheet?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply