August 2, 2014 at 6:44 am
/* Set up sample table Mark Fink */
CREATE TABLE Test(
[col1] [int] NULL,
[col2] [varchar](25) NULL,
[col3] [float] NULL,
[col4] [float] NULL,
[col5] [float] NULL,
[col6] [float] NULL,
[col7] [float] NULL,
[col8] [float] NULL,
[col9] [float] NULL,
[col10] [float] NULL,
[col11] [float] NULL,
[col12] [float] NULL,
[col13] [float] NULL,
[col14] [float] NULL,
[col15] [float] NULL,
[col16] [float] NULL,
[col17] [float] NULL,
[col18] [float] NULL,
[col19] [float] NULL,
[col20] [float] NULL,
[col21] [float] NULL,
[col22] [float] NULL,
[col23] [float] NULL,
[col24] [float] NULL,
[col25] [float] NULL,
[col26] [float] NULL,
[col27] [float] NULL,
[col28] [float] NULL
) ON [PRIMARY]
INSERT INTO Test
([col1],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10],[col11],[col12],[col13],[col14],[col15]
,[col16],[col17],[col18],[col19],[col20],[col21],[col22],[col23],[col24],[col25],[col26],[col27],[col28])
Select 1,'Row_1',2296.75,517.83,2814.58,0.484169675618118,1,131,1.03092783505155,-3315.55,60,60,2296.75,0.790284288750547,60,312045007.189998,0.682563839642543,175442.54,5512748,73998525.9300011 ,2821316,0.682563839642543,0,0,0,2685,983,1 union all
Select 2,'Row_2',456.18,49.99,506.17,0.0870723748152914,1,17,0.287769784172662,209.56,20,20,456.18,0.109004989373088,20,8674075.34000009,0.556342507842631,60669,210366,4487658.30999999,63531,0.556342507842631,0,0,0,432,245,0.560124809984799 union all
Select 3,'Row_3',211.73,167.91,379.64,0.0653064313864457,1,19,0.078125,165.34,38,38,211.73,0.0361051235284364,38,26985748.5100004,0.782001494727226,18775.47,1049805,14303288.1600001,101421,0.217998505272775,0,0,0,783,342,0.532975460122699 union all
Select 4, 'Row_4', 262.6, 197.5 ,460.1, 0.0791473213594555, 1, 27, 0.222222222222222, 167.71, 14.82, 14.82, 262.6, 0, 14.82 ,21297708.96,0.917246318987439 ,77648.8,1798422, 18821695.6800001,6324,0.0827536810125609,0,0,0,496,411,1 union all
Select 5, 'Row_5' ,58.74,135 ,193.74,0.0333275419260615,1,15,1.51515151515151,45.11, 8.03030303030303,8.03030303030303,58.74,0,8.03030303030303 ,3654577.84000001,0.996469424159128,52101.88,348930,3446315.88,480,0.00353057584087249,0,0,0,262,217,0.332675222112537 union all
Select 6, 'Row_6' ,599.22,195.96,795.18,0.136788452507307,1,29,1.11111111111111,476.46, 9.77777777777776,9.77777777777776,599.22,0.0395230701979169, 9.77777777777776 ,13134583.7500001 ,0.862834653911359 ,62663 ,556705 ,11654599.35 ,19080, 0.13716534608864, 0, 0, 0 ,335 ,255, 0.757575757575758 union all
Select 7, 'Row_7' ,479.12 ,97 ,576.12, 0.0991053135875016, 1 ,24 ,0.421940928270043, 326.6, 25.6, 25.6, 479.12 ,0.0152092974178864, 25.6, 12427404.6000001 ,0.524290560853778, 19814.2 ,590325 ,6327276.11999999 ,101760, 0.524290560853778, 0, 0, 0, 435, 227, 0.93717277486911 union all
Select 9, 'Row_9' ,0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.0001, 0, 0.0012, 93 ,0, 0 ,0 ,0 ,0, 0, 5, 5, 0 union all
Select 10, 'Row_10',39.71, 47.97,87.68,0.0150828887998197, 1,10,0.0979431929480901,66.47,50,50,39.71,0.00987323073212644, 50,2443242.54,0.799649968757086,14816.22,199014,1767865.38, 21414,0.200350031242915,0,0,0,406,206,0.468168462291871
/* View sample data */
Select * From Test
Declare @cols as nvarchar(max)
Declare @Rows table (r_id int identity not null,rowname nvarchar(100))
Declare @thisRow nvarchar(100)
Declare @i int
Declare @SQL nvarchar(max)
Declare @q1 char(1)
Set @q1 = CHAR(39)
/* Ok here you have to remove the un like column. All of my columns are numbers so I remove the test*/
Insert into @Rows(rowname) (Select quotename(C.name) from sys.columns as C
where C.object_id = object_id('Test')
and is_ansi_padded <> 1)
Set @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.col2) From Test as c For XML path(''), Type).value('.', 'nvarchar(max)') ,1,1,'')
Set @i = 1
Set @SQL = ''
While @i <= (select MAX(r_id) from @Rows)
begin
Select @thisRow = (Select rowname from @Rows where r_id = @i)
Select @SQL = @SQL + 'Select col2,' + @q1 + @thisRow + @q1 + ' as col, isnull(' + @thisRow + ',0) as Data from Test union all '
Set @i = @i +1
end
Select @SQL = 'Select *From (' + (Select Left(@SQL,LEN(@SQL) -9)) + ') as s PIVOT (max(Data) FOR col2 IN (' + @cols + ') ) as pvt'
Execute (@SQL)
Drop TABLE Test
August 2, 2014 at 12:59 pm
Please have a look at the CroosTab article referenced in my signature.
Get back to us if you still have questions.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply