January 7, 2015 at 2:36 am
I am trying to figure out how to pivot a temporary table.
I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)
So it could look like
Date , TS-Sales, Budget , Toms sales
01-Jan-14,100,120,300
02-Jan-14,80,150,300
03-Jan-14,100,20,180
Turned to this
01-jan-14, 02-jan-14, 03-jan-14
100,80,100
120,150,20
300,300,180
Or even just the date and a SUM
What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?
January 7, 2015 at 3:39 am
Something like this should work
CREATE TABLE #RawData
(
CalDate Date
,Value1 INT
,Value2 INT
,Value3 INT
)
INSERT INTO #RawData
VALUES ('01-Jan-2014',100,120,300),('02-Jan-2014',80,150,300),('03-Jan-2014',100,20,180)
Select * from #RawData
DECLARE @SQL NVARCHAR(4000) = 'SELECT '
DECLARE @SQLFrom NVARCHAR(4000) = 'FROM #RawData'
SET
@SQL=@SQL+
(
SELECT
STUFF
(
(
SELECT
', SUM(CASE WHEN CalDate = '''
+CONVERT(Nvarchar(10),CalDate)
+''' then Value1+Value2+Value3 else 0 end) AS ['
+CONVERT(Nvarchar(10),CalDate)
+']'
FROM #RawData
FOR XML PATH('')
)
,1,1,'')
)
+@SQLFrom
Print @SQL
EXEC sp_executesql @SQL
This will sum up the total, however there are a couple of problems, the length of the string, could breach the max for the sp_executesql.
Also if you want to split it out you would need to group by something else.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 7, 2015 at 3:48 am
Heres another example but with the values split out.
CREATE TABLE #RawData
(
CalDate Date
,Value1 INT
,Value2 INT
,Value3 INT
)
INSERT INTO #RawData
VALUES ('01-Jan-2014',100,120,300),('02-Jan-2014',80,150,300),('03-Jan-2014',100,20,180)
DECLARE @SQL NVARCHAR(4000) = 'SELECT '
DECLARE @SQLFrom NVARCHAR(4000) = 'FROM #UnpivtData Group by ValueName'
/*
Need to unpivot the raw data set so that VALUE1 etc can be grouped
*/
SELECT
Caldate,ValueName,Value
INTO #UnpivtData
FROM
#RawData
CROSS APPLY
(
VALUES
('Value1',Value1)
,('Value2',Value2)
,('Value3',Value3)
) x (ValueName,Value)
/*
build the Cross apply select
*/
SET
@SQL=@SQL+
(
SELECT
STUFF
(
(
SELECT
', SUM(CASE WHEN CalDate = '''
+CONVERT(Nvarchar(10),CalDate)
+''' then Value else 0 end) AS ['
+CONVERT(Nvarchar(10),CalDate)
+']'
FROM #RawData
FOR XML PATH('')
)
,1,1,'')
)
+@SQLFrom
Print @SQL
EXEC sp_executesql @SQL
drop table #RawData
Drop table #UnpivtData
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply