August 11, 2011 at 12:10 pm
Hi all. I'm quite aware that this problem is ridiculous, but I do need to solve it. Fortunately I already have a solution! Unfortunately it's a real messy one, and I'm sure it will do very badly when the size of the data grows. So I'm wondering if there is a more elegant one.
Sample data:
CREATE TABLE #Test
(
ID INT,
ID2 INT,
Val VARCHAR(10)
)
INSERT INTO #Test (ID, ID2, Val)
VALUES (1, 1, 'abc')
INSERT INTO #Test (ID, ID2, Val)
VALUES (1, 2, 'abddc')
INSERT INTO #Test (ID, ID2, Val)
VALUES (1, 2, 'abssc')
INSERT INTO #Test (ID, ID2, Val)
VALUES (2, 1, 'abc')
INSERT INTO #Test (ID, ID2, Val)
VALUES (2, 1, 'asbc')
Desired output:
IDID2COUNTVal
1NULL3NULL
NULL11NULL
NULLNULLNULL'abc'
NULL22NULL
NULLNULLNULL'abddc'
NULLNULLNULL'abssc'
2NULL2NULL
NULL12NULL
NULLNULLNULL'abc'
NULLNULLNULL'asbc'
And here's the code which I used to get the solution. It's ugly like I said, but it works:
CREATE TABLE #Test2
(
ID INT,
NullID INT,
ID2 INT,
NullID2 INT,
Val VARCHAR(10),
countTotal INT
)
;
WITH cte AS
(
SELECT ID, ID2, COUNT(*) AS countTotal
FROM #Test
GROUP BY ID, ID2 WITH ROLLUP
HAVING ID IS NOT NULL OR ID2 IS NOT NULL
)
INSERT INTO #Test2 (ID, NullID, ID2, NullID2, countTotal, val)
SELECT
c1.ID,
(CASE WHEN c1.ID2 IS NULL THEN c1.ID ELSE NULL END),
c1.ID2,
NULL,
(CASE WHEN c2.Val IS NULL THEN countTotal ELSE NULL END) AS countTotal,
c2.Val
FROM cte c1
LEFT JOIN #Test c2 ON c1.ID = c2.ID AND c1.ID2 = C2.ID2
UNION ALL
SELECT
ID,
NULL,
ID2,
ID2,
countTotal,
NULL AS 'Val'
FROM cte
WHERE ID IS NOT NULL AND ID2 IS NOT NULL
ORDER BY c1.ID, c1.ID2, countTotal DESC
SELECT
CAST(NullID AS NVARCHAR(20)) AS [ID],
CAST(NullID2 AS NVARCHAR(20)) AS [ID2],
CAST(countTotal AS NVARCHAR(50)) AS countTotal,
Val
FROM #Test2
So, can anyone think of a better solution? Or alternatively, is this solution actually not as bad as I thought it was?
August 11, 2011 at 2:33 pm
If you use the grouping() functions, you can do this without the temp table.
SELECT
CASE WHEN Grouping(ID2) = 1 THEN ID END AS ID
, CASE WHEN Grouping(Val) = 1 THEN ID2 END AS ID2
, CASE WHEN Grouping(Val) = 1 THEN Count(*) END
, Val
FROM #Test AS t
GROUP BY ID, ID2, Val
WITH ROLLUP
HAVING Grouping(ID) = 0
ORDER BY t.ID, Grouping(ID2) DESC, t.ID2, Grouping(Val) DESC
I'm not sure how much better it is.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply