November 18, 2011 at 1:24 am
I am trying to build a report for errors that i encounter when reconciling a customer report. The following gives me the list of customers that are reported as an error
TestNumber CustomerID
------------ -----------
1 A100
1 A200
1 A300
2 A200
I want to build a pivot report as:
CustomerID Test1 Test2
----------- ------- -------
A100 X -
A200 X X
A300 X -
Note: The test number can be any number of runs so hard-coding the Test Number as column heading is not an option. It has to be dynamic
How can this be achieved?
November 18, 2011 at 1:50 am
This was removed by the editor as SPAM
November 18, 2011 at 1:58 am
Need to be done via t-sql
November 18, 2011 at 2:55 am
This is the sort of thing you want: -
--First, lets create some sample data to play with
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
CREATE TABLE #testEnvironment
(ID INT IDENTITY PRIMARY KEY,
TestNumber INT NOT NULL,
CustomerID VARCHAR(4) NOT NULL);
--104,000 rows of data
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
big_tally AS (SELECT TOP 400 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM t4 x, t4 y),
little_tally AS (SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM t3 x, t3 y)
INSERT INTO #testEnvironment (CustomerID, TestNumber)
SELECT CustomerID, n AS TestNumber
FROM (SELECT chr + RIGHT('000' + CONVERT(VARCHAR(3),n),3) AS CustomerID
FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',t1.N,1) AS chr
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) t1(N)) a
CROSS JOIN little_tally) cust
CROSS JOIN big_tally
--Odd "tests"
DELETE FROM #testEnvironment
WHERE ID % 2 = 1
--Now on to your query
DECLARE @SQL AS VARCHAR(MAX), @Holder BIGINT
--Used to limit the tally table below for performance
SELECT @Holder = MAX(TestNumber)
FROM #testEnvironment
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM t4 x, t4 y)
SELECT @SQL = COALESCE(@SQL,'') + ', MAX(CASE WHEN TestNumber = ' + CONVERT(VARCHAR(6),n) + '
THEN ''X'' ELSE ''-'' END) AS [Test ' + CONVERT(VARCHAR(6),n) + ']'
FROM tally
WHERE n <= @Holder
SELECT @SQL = 'SELECT CustomerID, ' + STUFF(@SQL,1,1,'') +' FROM #testEnvironment GROUP BY CustomerID ORDER BY CustomerID'
EXEC (@SQL)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply