Cross tab SQL.
-
DECLARE @myTable TABLE
(
DataDate DATETIME,
TotalCount INT,
Count10s INT,
Count20s INT,
Count30s INT,
Count40s INT,
Count50s INT
)
INSERT @myTable
SELECT '01/01/2007', 100, 12, 8, 4, 5, 17
SELECT '02/01/2007', 120, 12, 12, 42, 5, 17
SELECT '03/01/2007', 140, 42, 8, 4, 15, 17
SELECT '04/01/2007', 160, 52, 18, 14, 5, 17
SELECT '05/01/2007', 250, 112, 28, 4, 25, 7
SELECT '06/01/2007', 270, 112, 28, 54, 25, 7
And I need a resultset like this.
CountType
Month1
Month2
Month3
Month4
Month5
Month6
Total
270
250
160
140
120
100
Count10s
112
112
52
42
12
12
Count20s
28
28
18
8
12
8
Count30s
54
4
14
4
42
4
Count40s
25
25
5
15
5
5
Count50s
7
7
17
17
17
17
Regards,
gova -
DECLARE
@myTable TABLE
(
DataDate
DATETIME,
TotalCount
INT,
Count10s
INT,
Count20s
INT,
Count30s
INT,
Count40s
INT,
Count50s
INT
)
INSERT
@myTable
SELECT
'01/01/2007', 100, 12, 8, 4, 5, 17 UNION ALL
SELECT
'02/01/2007', 120, 12, 12, 42, 5, 17 UNION ALL
SELECT
'03/01/2007', 140, 42, 8, 4, 15, 17 UNION ALL
SELECT
'04/01/2007', 160, 52, 18, 14, 5, 17 UNION ALL
SELECT
'05/01/2007', 250, 112, 28, 4, 25, 7 UNION ALL
SELECT
'06/01/2007', 270, 112, 28, 54, 25, 7
SELECT
theCategory AS CountType,
SUM(CASE WHEN theDate = 1 THEN theValue ELSE 0 END) AS Month1,
SUM(CASE WHEN theDate = 2 THEN theValue ELSE 0 END) AS Month2,
SUM(CASE WHEN theDate = 3 THEN theValue ELSE 0 END) AS Month3,
SUM(CASE WHEN theDate = 4 THEN theValue ELSE 0 END) AS Month4,
SUM(CASE WHEN theDate = 5 THEN theValue ELSE 0 END) AS Month5,
SUM(CASE WHEN theDate = 6 THEN theValue ELSE 0 END) AS Month6,
SUM(CASE WHEN theDate = 7 THEN theValue ELSE 0 END) AS Month7,
SUM(CASE WHEN theDate = 8 THEN theValue ELSE 0 END) AS Month8,
SUM(CASE WHEN theDate = 9 THEN theValue ELSE 0 END) AS Month9,
SUM(CASE WHEN theDate = 10 THEN theValue ELSE 0 END) AS Month10,
SUM(CASE WHEN theDate = 11 THEN theValue ELSE 0 END) AS Month11,
SUM(CASE WHEN theDate = 12 THEN theValue ELSE 0 END) AS Month12,
SUM(CASE WHEN theDate < 1 OR theDate > 12 THEN theValue ELSE 0 END) AS Other
FROM
(
SELECT DATEDIFF(MONTH, DataDate, CURRENT_TIMESTAMP) AS theDate, 'Total' AS theCategory, TotalCount AS theValue FROM @MyTable UNION ALL
SELECT DATEDIFF(MONTH, DataDate, CURRENT_TIMESTAMP), 'Count10s', Count10s FROM @MyTable UNION ALL
SELECT DATEDIFF(MONTH, DataDate, CURRENT_TIMESTAMP), 'Count20s', Count20s FROM @MyTable UNION ALL
SELECT DATEDIFF(MONTH, DataDate, CURRENT_TIMESTAMP), 'Count30s', Count30s FROM @MyTable UNION ALL
SELECT DATEDIFF(MONTH, DataDate, CURRENT_TIMESTAMP), 'Count40s', Count40s FROM @MyTable UNION ALL
SELECT DATEDIFF(MONTH, DataDate, CURRENT_TIMESTAMP), 'Count50s', Count50s FROM @MyTable
) AS d
GROUP
BY theCategory
ORDER
BY CASE WHEN theCategory = 'Total' THEN 0 ELSE 1 END,
theCategory
N 56°04'39.16"
E 12°55'05.25" -
Keep in mind that Peter's "CASE" solution, which is indeed ideal if you have a fixed number of columns, won't work if you have a dynamic number of columns to work with (you didn't specify in the OP, so it might not be applicable to you). If the number of columns is dependent on the passed date range, for instance, you'll likely have to take the dynamic SQL route. Alternatively, if this is for a report, you can bypass both methods, and let the reporting tool take care of it, as most of them can pivot data like a charm.
-
Thanks Peter. That will work. I got fixed columns so this solutuon is okay.
Some how I did like this. But your solution is better.
DECLARE @myTable TABLE
(
DataDate DATETIME,
TotalCount INT,
Count10s INT,
Count20s INT,
Count30s INT,
Count40s INT,
Count50s INT
)
INSERT @myTable
SELECT '01/01/2007', 100, 12, 8, 4, 5, 17 UNION
SELECT '02/01/2007', 120, 12, 12, 42, 5, 17 UNION
SELECT '03/01/2007', 140, 42, 8, 4, 15, 17 UNION
SELECT '04/01/2007', 160, 52, 18, 14, 5, 17 UNION
SELECT '05/01/2007', 250, 112, 28, 4, 25, 7 UNION
SELECT '06/01/2007', 270, 112, 28, 54, 25, 7
DECLARE @TRows TABLE
(
TblRow VARCHAR(128),
ColOrder SMALLINT
)
INSERT @TRows
SELECT 'TotalCount', 1 UNION
SELECT 'Count10s', 2 UNION
SELECT 'Count20s', 3 UNION
SELECT 'Count30s', 4 UNION
SELECT 'Count40s', 5 UNION
SELECT 'Count50s', 6
SELECT TblRow,
Month1 = SUM(CASE WHEN DataDate = '06/01/2007' THEN
CASE TblRow WHEN 'TotalCount' THEN TotalCount
WHEN 'Count10s' THEN Count10s
WHEN 'Count20s' THEN Count20s
WHEN 'Count30s' THEN Count30s
WHEN 'Count40s' THEN Count40s
WHEN 'Count50s' THEN Count50s
END
ELSE NULL END),
Month2 = SUM(CASE WHEN DataDate = '05/01/2007' THEN
CASE TblRow WHEN 'TotalCount' THEN TotalCount
WHEN 'Count10s' THEN Count10s
WHEN 'Count20s' THEN Count20s
WHEN 'Count30s' THEN Count30s
WHEN 'Count40s' THEN Count40s
WHEN 'Count50s' THEN Count50s
END
ELSE NULL END),
Month3 = SUM(CASE WHEN DataDate = '04/01/2007' THEN
CASE TblRow WHEN 'TotalCount' THEN TotalCount
WHEN 'Count10s' THEN Count10s
WHEN 'Count20s' THEN Count20s
WHEN 'Count30s' THEN Count30s
WHEN 'Count40s' THEN Count40s
WHEN 'Count50s' THEN Count50s
END
ELSE NULL END),
Month4 = SUM(CASE WHEN DataDate = '03/01/2007' THEN
CASE TblRow WHEN 'TotalCount' THEN TotalCount
WHEN 'Count10s' THEN Count10s
WHEN 'Count20s' THEN Count20s
WHEN 'Count30s' THEN Count30s
WHEN 'Count40s' THEN Count40s
WHEN 'Count50s' THEN Count50s
END
ELSE NULL END),
Month5 = SUM(CASE WHEN DataDate = '02/01/2007' THEN
CASE TblRow WHEN 'TotalCount' THEN TotalCount
WHEN 'Count10s' THEN Count10s
WHEN 'Count20s' THEN Count20s
WHEN 'Count30s' THEN Count30s
WHEN 'Count40s' THEN Count40s
WHEN 'Count50s' THEN Count50s
END
ELSE NULL END),
Month6 = SUM(CASE WHEN DataDate = '01/01/2007' THEN
CASE TblRow WHEN 'TotalCount' THEN TotalCount
WHEN 'Count10s' THEN Count10s
WHEN 'Count20s' THEN Count20s
WHEN 'Count30s' THEN Count30s
WHEN 'Count40s' THEN Count40s
WHEN 'Count50s' THEN Count50s
END
ELSE NULL END)
FROM
@myTable M
CROSS JOIN
@TRows T
GROUP BY
TblRow, ColOrder
ORDER BY ColOrder
Regards,
gova -
Peter, shouldn't your derived table look like this?
SELECT MONTH(DataDate) AS theDate, 'Total' AS theCategory, TotalCount AS theValue FROM @MyTable UNION ALL
SELECT MONTH(DataDate), 'Count10s', Count10s FROM @MyTable UNION ALL
SELECT MONTH(DataDate), 'Count20s', Count20s FROM @MyTable UNION ALL
SELECT MONTH(DataDate), 'Count30s', Count30s FROM @MyTable UNION ALL
SELECT MONTH(DataDate), 'Count40s', Count40s FROM @MyTable UNION ALL
SELECT MONTH(DataDate), 'Count50s', Count50s FROM @MyTable
HTH
Dave J
-
No, because MonthX is denoted how many months back the data is.
Look at the sample data. THe data for June is in column Month1, and the data for January is in column Month6.
N 56°04'39.16"
E 12°55'05.25" -
Oops!
Yours is an excellent method. I looked at the results and thought "That's odd." But you're Spot on.
Dave J
-
Thanks.
N 56°04'39.16"
E 12°55'05.25"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply