Friday, June 04, 2010 8:29 PM
I have data in multiple columns that I would like to get into grouped rows. Example of data format (This is one row): ID1, ID1Data1, ID1Data2, ID2, ID2Data1, ID2Data2. There are a fixed number of IDs but it needs to be flexable to add more.
I would like the data to be like this (Two rows):
ID1, ID1Data1, ID1Data2
ID2, ID2Data1, ID2Data2
How can I convert this? I have looked at the unpivot but just can't get it to work how I need it. I know I could use a union but I have a bunch of IDs.
Friday, June 04, 2010 8:55 PM
Is there anything (prefix for example) in the data to distinguish an ID field from a Data field?
Is there always the same number of Data fields for each ID field such that the ID fields are always every 3rd field?
----------------------------------------------------------- May we all make money in the sequel.
Friday, June 04, 2010 9:27 PMModerator
If the number of same columns is always known in advance (3 in this case) try this solution:
create table TestIDS (ID1 int, ID1Data1 int, ID1Data2 int, ID2 int, ID2Data1 int, ID2Data2 int, ID3 int, ID3Data1 int, ID3Data2 int, ID4 int, ID4Data1 int, ID4Data2 int) insert into TestIDS values (1,2,3, 2,5,6, 3,7,8, 4,10,12) go declare @SQL nvarchar(max), @Total_Columns int select @Total_Columns = COUNT(1) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TestIDs' set @SQL = ' SELECT ' select @SQL = @SQL + Column_Name + Case when Ordinal_Position = 1 then ' AS ID' when Ordinal_Position = 2 then ' AS Data1' when Ordinal_Position = 3 then ' AS Data2' else '' end + Case when Ordinal_Position = @Total_Columns then ' FROM TestIDs' when ORDINAL_POSITION /3.0 = ORDINAL_POSITION / 3 then CHAR (13) + ' FROM TestIDs UNION ALL SELECT ' else ', ' end from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TestIDs' order by Ordinal_Posiiton --print @SQL execute(@SQL)
Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
- Marked As Answer by KJian_ Thursday, June 10, 2010 9:57 AM
Saturday, June 05, 2010 2:15 AMPlease, provide sample table with data and expected output