December 11, 2011 at 9:32 pm
Hi,
I am using SQL 2008 and I would like to transpose rows to columns as follows:
here is the original data in rows:
Claimant ClaimClaim
NumberNumberDate
a 1 1-Jul-2000
a22-Jul-2000
a35-Jul-2000
a412-Jul-2000
b11-Dec-2000
b215-Dec-2000
b316-Jan-2001
c11-Jun-2011
c210-Jul-2011
ClaimantClaim1Date1Claim2Date2DateDiff2Claim3Date3DateDiff3Claim4Date4DateDiff4 etc up to 57 claims
a11-Jul-200022-Jul-2000135-Jul-20003 4 12-Jul-2000 7
b11-Dec-2000214-Dec-200013316-Jan-20012
c11-Jun-2011210-Jul-201140
etc up to 400,000 claimant rows
Is there an esier way to do this appart from using nested cursors?
Note that the claims must be set from one to however many there are for the claimant in date order up to a maximum of 57. Also the DateDiff column is the difference between the current date and the previous claim date.
Is there anyone who can help?
December 12, 2011 at 12:44 am
Have a look a Jeff Moden's article about crosstab's, the link is in my footer text.
with cteClaims as (
select row_number() over (partition by c.[Claimant Number] order by c.[Claim Date]) as nr,
c.*
from dbo.Claims c
)
select c1.[Claimant Number],
max(case c1.nr when 1 then c1.[Claim Number] end) as [Claim1],
max(case c1.nr when 1 then c1.[Date] end) as [Date1],
max(case c1.nr when 2 then c1.[Claim Number] end) as [Claim2],
max(case c1.nr when 2 then c1.[Date] end) as [Date2],
max(case c1.nr when 2 then datediff(day, c1.[Date], c2.[Date]) end) as [DateDiff2],
max(case c1.nr when 3 then c1.[Claim Number] end) as [Claim3],
max(case c1.nr when 3 then c1.[Date] end) as [Date3],
max(case c1.nr when 3 then datediff(day, c1.[Date], c2.[Date]) end) as [DateDiff3]
from cteClaims c1
left outer join cteClaims c2 on (c2.[Claimant Number] = c1.[Claimant Number] and c2.nr = c1.nr - 1)
group by c1.Claimant
The code provided is untested as you didn't include any DDL with your question... Please also read Jeff's other article I linked to, to learn what the benefits are to do so next time you ask a question.
December 13, 2011 at 4:25 pm
Thank you for the code, but you are assuming that you know the value of claim number and that claim number value is always 1,2,3 etc
this is not true I only gave an example, the claim number can be any number like 12334232 and you cannot use the value of the claim number to indicate the order of the column to transpose to.
Therefore claim_id value 1234 can be transposed to Claim5 where as claim_id value 4321 can be transposed in calim1, therefore the order of transpose for the claim is dictated by claim date not the value of the claim.
December 14, 2011 at 2:49 am
BEGIN TRAN
--First, let's create some sample data to play with!!
SELECT [Claimant Number], --This comes from your sample data
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS [Claim Number], --This is a pseudo-randomly generated number (1-100),
--to show that the "Claim Number" is not important in "converting rows to columns"
[Claim Date] --This comes from your sample data
INTO yourTable --This is the table we'll be using your your query
FROM (SELECT 'a', CONVERT(DATETIME,'1-Jul-2000')
UNION ALL SELECT 'a', '2-Jul-2000'
UNION ALL SELECT 'a', '5-Jul-2000'
UNION ALL SELECT 'a', '12-Jul-2000'
UNION ALL SELECT 'b', '1-Dec-2000'
UNION ALL SELECT 'b', '15-Dec-2000'
UNION ALL SELECT 'b', '16-Jan-2001'
UNION ALL SELECT 'c', '1-Jun-2011'
UNION ALL SELECT 'c', '10-Jul-2011') a([Claimant Number], [Claim Date])
SELECT * FROM yourTable --Just to show you how the table looks
--OK, so how do we go about building your query?
--First, let's see if we can determine the "Claim Number" to use as your heading
SELECT [Claimant Number], [Claim Number], [Claim Date],
ROW_NUMBER() OVER(PARTITION BY [Claimant Number] ORDER BY [Claim Date]) AS N --This is how we are going to figure out the
--"Claim Number" to use as the heading.
FROM yourTable
--OK, now R.P.Rozema has already done most of the work in showing us the final query we
--want to end up with.
SELECT a.[Claimant Number],
MAX(CASE a.N WHEN 1 THEN a.[Claim Number] END) AS [Claim1],
MAX(CASE a.N WHEN 1 THEN a.[Claim Date] END) AS [Date1],
MAX(CASE a.N WHEN 2 THEN DATEDIFF(dd, a.[Claim Date], b.[Claim Date]) END) AS [DateDiff2]
FROM (SELECT [Claimant Number], [Claim Number], [Claim Date],
ROW_NUMBER() OVER(PARTITION BY [Claimant Number] ORDER BY [Claim Date]) AS N
FROM yourTable) a
LEFT OUTER JOIN (SELECT [Claimant Number], [Claim Number], [Claim Date],
ROW_NUMBER() OVER(PARTITION BY [Claimant Number] ORDER BY [Claim Date]) AS N
FROM yourTable) b ON b.[Claimant Number] = b.[Claimant Number] AND b.N = a.N - 1
GROUP BY a.[Claimant Number]
--But we need this to do a little more dynamic
--THIS IS THE ACTUAL SOLUTION
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + ', MAX(CASE a.N WHEN '+ CONVERT(VARCHAR(2),N) +' THEN a.[Claim Number] END) AS [Claim'+ CONVERT(VARCHAR(2),N)+'],
MAX(CASE a.N WHEN '+ CONVERT(VARCHAR(2),N) +' THEN a.[Claim Date] END) AS [Date'+ CONVERT(VARCHAR(2),N)+'],
MAX(CASE a.N WHEN 2 THEN DATEDIFF(dd, a.[Claim Date], b.[Claim Date]) END) AS [DateDiff'+ CONVERT(VARCHAR(2),N)+']'
FROM (SELECT DISTINCT N
FROM (SELECT [Claimant Number], [Claim Number], [Claim Date],
ROW_NUMBER() OVER(PARTITION BY [Claimant Number] ORDER BY [Claim Date]) AS N
FROM yourTable) a ) b
WHERE N <> 1
SET @SQL = 'SELECT a.[Claimant Number], MAX(CASE a.N WHEN 1 THEN a.[Claim Number] END) AS [Claim1], MAX(CASE a.N WHEN 1 THEN a.[Claim Date] END) AS [Date1]'+@SQL
SET @SQL = @SQL + ' FROM (SELECT [Claimant Number], [Claim Number], [Claim Date],'
+ ' ROW_NUMBER() OVER(PARTITION BY [Claimant Number] ORDER BY [Claim Date]) AS N FROM yourTable) a'
+ ' LEFT OUTER JOIN (SELECT [Claimant Number], [Claim Number], [Claim Date],'
+ ' ROW_NUMBER() OVER(PARTITION BY [Claimant Number] ORDER BY [Claim Date]) AS N FROM yourTable) b'
+ ' ON b.[Claimant Number] = b.[Claimant Number] AND b.N = a.N - 1 GROUP BY a.[Claimant Number]'
EXEC(@SQL)
ROLLBACK
Actual solution, without the sample data-
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + ', MAX(CASE a.N WHEN '+ CONVERT(VARCHAR(2),N) +' THEN a.[Claim Number] END) AS [Claim'+ CONVERT(VARCHAR(2),N)+'],
MAX(CASE a.N WHEN '+ CONVERT(VARCHAR(2),N) +' THEN a.[Claim Date] END) AS [Date'+ CONVERT(VARCHAR(2),N)+'],
MAX(CASE a.N WHEN 2 THEN DATEDIFF(dd, a.[Claim Date], b.[Claim Date]) END) AS [DateDiff'+ CONVERT(VARCHAR(2),N)+']'
FROM (SELECT DISTINCT N
FROM (SELECT [Claimant Number], [Claim Number], [Claim Date],
ROW_NUMBER() OVER(PARTITION BY [Claimant Number] ORDER BY [Claim Date]) AS N
FROM yourTable) a ) b
WHERE N <> 1
SET @SQL = 'SELECT a.[Claimant Number], MAX(CASE a.N WHEN 1 THEN a.[Claim Number] END) AS [Claim1], MAX(CASE a.N WHEN 1 THEN a.[Claim Date] END) AS [Date1]'+@SQL
SET @SQL = @SQL + ' FROM (SELECT [Claimant Number], [Claim Number], [Claim Date],'
+ ' ROW_NUMBER() OVER(PARTITION BY [Claimant Number] ORDER BY [Claim Date]) AS N FROM yourTable) a'
+ ' LEFT OUTER JOIN (SELECT [Claimant Number], [Claim Number], [Claim Date],'
+ ' ROW_NUMBER() OVER(PARTITION BY [Claimant Number] ORDER BY [Claim Date]) AS N FROM yourTable) b'
+ ' ON b.[Claimant Number] = b.[Claimant Number] AND b.N = a.N - 1 GROUP BY a.[Claimant Number]'
EXEC(@SQL)
December 14, 2011 at 8:38 am
adrian.buzgau (12/13/2011)
Thank you for the code, but you are assuming that you know the value of claim number and that claim number value is always 1,2,3 etcthis is not true I only gave an example, the claim number can be any number like 12334232 and you cannot use the value of the claim number to indicate the order of the column to transpose to.
Therefore claim_id value 1234 can be transposed to Claim5 where as claim_id value 4321 can be transposed in calim1, therefore the order of transpose for the claim is dictated by claim date not the value of the claim.
Have you tried it? Because this query does not assume the claim number to be numbered 1, 2, 3, etc. Instead it uses the row_number() window function to generate a sequence number that does follow this pattern and uses that for the ordering. The sequence number is determined by the Claim Date column and (re)starting from 1 for each Claimant Number, so it should follow your request exactly.
Had you provided DDL plus some test data, I would have given you a working sample so you can see the results for yourself. Without this you'll have to make it work on your tables/data yourself.
Since Cadavre was nice enough to provide a script to generate some test data I've adapted the example to work on that.
with cteClaims as (
select row_number() over (partition by c.[Claimant Number] order by c.[Claim Date]) as nr,
c.*
from dbo.Claims c
)
select c1.[Claimant Number],
max(case c1.nr when 1 then c1.[Claim Number] end) as [Claim1],
max(case c1.nr when 1 then c1.[Claim Date] end) as [Date1],
max(case c1.nr when 2 then c1.[Claim Number] end) as [Claim2],
max(case c1.nr when 2 then c1.[Claim Date] end) as [Date2],
max(case c1.nr when 2 then datediff(day, c2.[Claim Date], c1.[Claim Date]) end) as [DateDiff2],
max(case c1.nr when 3 then c1.[Claim Number] end) as [Claim3],
max(case c1.nr when 3 then c1.[Claim Date] end) as [Date3],
max(case c1.nr when 3 then datediff(day, c2.[Claim Date], c1.[Claim Date]) end) as [DateDiff3]
from cteClaims c1
left outer join cteClaims c2 on (c2.[Claimant Number] = c1.[Claimant Number] and c2.nr = c1.nr - 1)
group by c1.[Claimant Number]
The output below does not really fit on the screen, but you should still be able to make out of it that the claim numbers for claimant 'b' are 34, 82 and 22, in that order. i.e. Ordered by date, not claim number. To get to up to 57 claims, all you need to do is add more columns in the group by query...
Claimant Number Claim1 Date1 Claim2 Date2 DateDiff2 Claim3 Date3 DateDiff3
--------------- ----------- ----------------------- ----------- ----------------------- ----------- ----------- ----------------------- -----------
a 18 2000-07-01 00:00:00.000 80 2000-07-02 00:00:00.000 1 43 2000-07-05 00:00:00.000 3
b 34 2000-12-01 00:00:00.000 82 2000-12-15 00:00:00.000 14 22 2001-01-16 00:00:00.000 32
c 60 2011-06-01 00:00:00.000 82 2011-07-10 00:00:00.000 39 NULL NULL NULL
December 14, 2011 at 3:02 pm
I got it now, I have not paid attention to the logic enough.
It's great precisely what I needed.:w00t:
December 14, 2011 at 7:27 pm
adrian.buzgau (12/14/2011)
I got it now, I have not paid attention to the logic enough.It's great precisely what I needed.:w00t:
Do you understand how it actually works?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2011 at 7:37 pm
I think I do understand how it works,
First it creates a number 1,2,3,4 for each claim within the claimant claims and then starts from 1,2,3,4 again for the next claimant
Then based on the same claimant it checks the number created previously and assigns to column(claim1, claim2 etc)
It does the difference between dates for the previous number and assigns the difference in the DaysDiff1 column.
I think that's it, it has helped me a lot do research for WorkSafe and TAC datasets. Thank you to all who contributed.:-D
December 16, 2011 at 11:02 pm
Basically correct. Just making sure. Remember that it's also aggregating the data as it "pivots" the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply