December 9, 2011 at 9:40 am
Identifierpriority accountnumber
12345619999999999
12345618888888888
12345650000000001
123456471111111111
67890112222222222
67890120101010101
01234419998887767
98765910890970707
87650117645387076
Above represents sample data from my table, in total it has 1.2mil records. As you can see the Identifier field can have multiple
identical records, each with a Priority number and a unique account number. It's possible for the same Priority number to exist for
the same Identifier.
I need create a new table, transforming (my word) the accountnumber values from rows to columns, by Priority in ascending order. In the
event there are duplicate Priority value, just take the first accountnumber. So, my new table would look like this:
Identifieraccount1account2account3account4 ....... up to 56 account fields
1234569999999999888888888800000000011111111111
67890122222222220101010101
0123449998887767
9876590890970707
8765017645387076
December 9, 2011 at 10:13 am
BEGIN TRAN
--Create sample data to play with
SELECT Identifier, priority, accountnumber
INTO #SampleData
FROM (SELECT 123456, 1, 9999999999
UNION ALL SELECT 123456, 1, 8888888888
UNION ALL SELECT 123456, 5, 0000000001
UNION ALL SELECT 123456, 47, 1111111111
UNION ALL SELECT 678901, 1, 2222222222
UNION ALL SELECT 678901, 2, 0101010101
UNION ALL SELECT 012344, 1, 9998887767
UNION ALL SELECT 987659, 1, 0890970707
UNION ALL SELECT 876501, 1, 7645387076) a(Identifier, priority, accountnumber)
--Build Query
DECLARE @SQL AS VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL, '') + 'MAX(CASE WHEN accountID = ''' + CONVERT(VARCHAR(10),accountID) + '''
THEN accountnumber ELSE NULL END) AS ['+ CONVERT(VARCHAR(10),accountID)+'], '
FROM (SELECT DISTINCT
'account'+CONVERT(VARCHAR(2),ROW_NUMBER() OVER (PARTITION BY Identifier ORDER BY priority)) AS accountID
FROM (SELECT Identifier, priority, accountnumber,
ROW_NUMBER() OVER (PARTITION BY Identifier, priority ORDER BY (SELECT NULL)) AS rn
FROM #SampleData) a
WHERE rn = 1) workQuery
--Second part of query
SET @SQL = 'SELECT Identifier, ' + SUBSTRING(@SQL,1,LEN(@SQL)-1) +
'FROM (SELECT Identifier, priority, accountnumber,
''account''+CONVERT(VARCHAR(2),ROW_NUMBER() OVER (PARTITION BY Identifier ORDER BY priority)) AS accountID
FROM (SELECT Identifier, priority, accountnumber,
ROW_NUMBER() OVER (PARTITION BY Identifier, priority ORDER BY (SELECT NULL)) AS rn
FROM #SampleData) a
WHERE rn = 1) workQuery
GROUP BY Identifier'
--Execute Query
EXEC(@SQL)
ROLLBACK
December 9, 2011 at 10:13 am
The solution to your question usually is referred to as PIVOT or CrossTab query. A link to an related article referencing the latter can be found in my signature.
In general, formatting like that should be done at the presentation layer. If this isn't an option and it needs to be hard coded at the SQL level, one should be aware of the tasks being involved to both, control and react, if the max number (56 in your case) will be exceeded. It might be an option to use a DynamicCrossTab approach (see the related link in my signature for details), but this goes beyond the basic level...
For your specific scenario you could use the following solution (please note how I presented the sampel data in a ready to use format to make it easier for others working on a solution rather than creating the test scenario):
DECLARE @tbl TABLE
(
Identifier INT , priority INT, accountnumber CHAR(10)
)
INSERT INTO @tbl
VALUES (123456,1, 9999999999),
(123456, 1, 8888888888),
(123456, 5, 0000000001),
(123456, 47, 1111111111),
(678901, 1, 2222222222),
(678901, 2, 0101010101),
(012344, 1, 9998887767),
(987659, 1, 0890970707),
(876501, 1, 7645387076)
;
WITH cte AS
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY priority ) AS accountPos
FROM @tbl
)
SELECT
identifier,
MAX(CASE WHEN accountPos = 1 THEN accountnumber ELSE '' END) AS account1,
MAX(CASE WHEN accountPos = 2 THEN accountnumber ELSE '' END) AS account2,
MAX(CASE WHEN accountPos = 3 THEN accountnumber ELSE '' END) AS account3,
MAX(CASE WHEN accountPos = 4 THEN accountnumber ELSE '' END) AS account4,
MAX(CASE WHEN accountPos = 56 THEN accountnumber ELSE '' END) AS account56
FROM cte
GROUP BY identifier
ORDER BY identifier
December 9, 2011 at 12:15 pm
Lutzm,
Still testing but it looks nearly perfect!
I just need a couple more things:
How do I insert the results into a new table (or update the account fields in the existing table)?
How can I add another field(s) to the result set?
Thank you. Do you do consulting? May I contact you directly?
SG
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply