How can I use SQL Pivot to do this?
I have a dataset that is organized like this:
Timestamp|A0001|A0002|A0003|A0004|B0001|B0002|B0003|B0004 ...
---------+-----+-----+-----+-----+-----+-----+-----+-----
2008-1-1 | 1 | 2 | 10 | 6 | 20 | 35 | 300 | 8
2008-1-2 | 5 | 2 | 9 | 3 | 50 | 38 | 290 | 2
2008-1-4 | 7 | 7 | 11 | 0 | 30 | 87 | 350 | 0
2008-1-5 | 1 | 9 | 1 | 0 | 25 | 100 | 10 | 0
...
Where A0001 is the A value of item # 1 and B0001 is the B value of item # 1. There can be over 60 different items in a table, and each item has a "Value" column and a "Value" column, which means a total of over 120 columns in the table.
Where I want to get a 3 column result (Item index, A Value, B Value) that sums the A and B values for each item:
Index | A Value | B Value
------+---------+--------
0001 | 14 | 125
0002 | 20 | 260
0003 | 31 | 950
0004 | 9 | 10
....
Since I am going from columns to rows, I would expect a pivot in the solution, but I am not sure how to implement it. Part of the problem is how to delimit A and B to form the values for the Index column. The other part is that I've never had to use Pivot before, so I'm bumping into the basic syntax too.
I think I ultimately need to have a multi-step solution that first builds sums like:
ColName | Value
--------+------
A0001 | 14
A0002 | 20
A0003 | 31
A0004 | 9
B0001 | 125
B0002 | 260
B0003 | 950
B0004 | 10
Then change the ColName data to slice the index:
ColName | Value | Index | Aspect
--------+-------+-------+-------
A0001 | 14 | 0001 | A
A0002 | 20 | 0002 | A
A0003 | 31 | 0003 | A
A0004 | 9 | 0004 | A
B0001 | 125 | 0001 | B
B0002 | 260 | 0002 | B
B0003 | 950 | 0003 | B
B0004 | 10 | 0004 | B
Finally, join to move the B values next to the A values.
It seems like a long process to get what I want. Therefore, I am after consultation about whether I am on the right path, or if there is another approach that I have already considered, will make my life easier.
Note 1) Solution should be in T-SQL on MSSQL 2005.
Note 2) The format of the table cannot be changed.
Edit Another method I was thinking uses UNION and separate SUM () s for each column:
SELECT '0001' as Index, SUM(A0001) as A, SUM(B0001) as B FROM TABLE
UNION
SELECT '0002' as Index, SUM(A0002) as A, SUM(B0002) as B FROM TABLE
UNION
SELECT '0003' as Index, SUM(A0003) as A, SUM(B0003) as B FROM TABLE
UNION
SELECT '0004' as Index, SUM(A0004) as A, SUM(B0004) as B FROM TABLE
UNION
...
But this approach really doesn't look good.
EDIT There are 2 great answers so far. But I would like to add two more conditions for the request :-)
1) I need to select rows based on a range of timestamps (minv <timestamp <maxv).
2) I also need to conditionally select rows in a UDF that handles the timestamp
Using Brettsky table names, the above translates to:
...
(SELECT A0001, A0002, A0003, B0001, B0002, B0003
FROM ptest
WHERE timestamp>minv AND timestamp<maxv AND fn(timestamp)=fnv) p
unpivot
(val for item in (A0001, A0002, A0003, B0001, B0002, B0003)) as unpvt
...
Considering I'm conditionally adding the fn () requirement, I think I also need to go down the dynamic SQL path as suggested by Jonathan. Moreover, I need to build the same query for 12 different tables - all of the same style.
source to share
Same answer to this question, it was fun:
-- Get column names from system table
DECLARE @phCols NVARCHAR(2000)
SELECT @phCols = COALESCE(@phCols + ',[' + name + ']', '[' + name + ']')
FROM syscolumns WHERE id = (select id from sysobjects where name = 'Test' and type='U')
-- Get rid of the column we don't want
SELECT @phCols = REPLACE(@phCols, '[Timestamp],', '')
-- Query & sum using the dynamic column names
DECLARE @exec nvarchar(2000)
SELECT @exec =
'
select
SUBSTRING([Value], 2, LEN([Value]) - 1) as [Index],
SUM(CASE WHEN (LEFT([Value], 1) = ''A'') THEN Cols ELSE 0 END) as AValue,
SUM(CASE WHEN (LEFT([Value], 1) = ''B'') THEN Cols ELSE 0 END) as BValue
FROM
(
select *
from (select ' + @phCols + ' from Test) as t
unpivot (Cols FOR [Value] in (' + @phCols + ')) as p
) _temp
GROUP BY SUBSTRING([Value], 2, LEN([Value]) - 1)
'
EXECUTE(@exec)
You don't need to specify hardcode column names in this file.
source to share
Ok I have come up with one solution that should get you started. It will probably take some time to put together, but will work well. It would be nice if we didn't have to list all columns by name.
Basically it is using UNPIVOT and placing that product in the temp table and then querying it into your final dataset. I called my table ptest, when I put this together, this is the one that contains all A0001 columns, etc.
-- Create the temp table
CREATE TABLE #s (item nvarchar(10), val int)
-- Insert UNPIVOT product into the temp table
INSERT INTO #s (item, val)
SELECT item, val
FROM
(SELECT A0001, A0002, A0003, B0001, B0002, B0003
FROM ptest) p
unpivot
(val for item in (A0001, A0002, A0003, B0001, B0002, B0003)) as unpvt
-- Query the temp table to get final data set
SELECT RIGHT(item, 4) as item1,
Sum(CASE WHEN LEFT(item, 1) = 'A' THEN val ELSE 0 END) as A,
Sum(CASE WHEN LEFT(item, 1) = 'B' THEN val ELSE 0 END) as B
from #s
GROUP BY RIGHT(item, 4)
-- Delete temp table
drop table #s
By the way, thanks for the question, this was the first time I used UNPIVOT. I always wanted, I just never needed.
source to share