July 21, 2010 at 11:45 am
I have been studying Pivot, but don't see a way to use that for my goal. Please correct me if I am wrong.
My goal is to return the first 5-12 records in a sub-query or join to my "parent" query and send the results out as columns instead of rows.
Table1
Name
Address
Phone
ClaimNumber
etc.
Table2
ClaimNumber
ClaimCode
I would normally join the ClaimNumber field as a key and return duplicate records for the Table1 side of the query, but in this case I want the "ClaimCode" values in Table2 to come back as 5 to 12 columns I can output to a file.
The result would look like this:
ClaimNumber, Name, Address, Phone, ClaimCode1, ClaimCode2, ClaimCode3, ClaimCode4, Claimcode5.
Joe, 1234 Road, 747-3000, 21, 23, 54, 26, 83
Instead of:
Joe, 1234 Road, 747-3000, 21
Joe, 1234 Road, 747-3000, 23
Joe, 1234 Road, 747-3000, 54
Joe, 1234 Road, 747-3000, 26
Joe, 1234 Road, 747-3000, 83
I though about the Pivot, but I think that requires some kind of aggregation, which I do not need in this case.
I was thinking this would be done by something like:
Select ClaimNumber, Name, Address, Phone,
ConvertToColumns(Select Top 5 T2.ClaimCode
From Table2 T2
Where T2.ClaimNumber = T1.ClaimNumber
Order by ClaimCode)
From Table1 T1
Where ClaimNumber = 1234
Is what I am asking for possible?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
July 21, 2010 at 12:18 pm
here's a dynamic pivot table approach. Just note that if access is granted through a role, then specific select privileges are needed to run this dynamically
create table #t1 (name varchar(20), Address varchar(20), ClaimNumber int, Phone varchar(20))
create table #t2 (ClaimNumber int, ClaimCode smallint)
insert into #t1
select 'Joe', '1234 Road',1, '111-1111' union all
select 'Sue', '456 Road',2, '222-2222' union all
select 'Mike', '78 Street',3, '333-3333' union all
select 'Jim', '1 Main',4, '444-4444'
insert into #t2
select 1, 21 union all
select 1, 23 union all
select 1, 54 union all
select 1, 26 union all
select 1, 83 union all
select 2, 27 union all
select 2, 30 union all
select 3, 54
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT ClaimCode
FROM #t2
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
EXEC ('SELECT *
FROM
(
SELECT a.name, a.Address, a.ClaimNumber, a.Phone, ClaimCode
FROM #t1 a inner join #t2 b
on a.ClaimNumber = b.ClaimNumber
) t
PIVOT (avg(ClaimCode) FOR ClaimCode IN (' + @ColsList + ')) PVT')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 21, 2010 at 1:58 pm
That looks like a winner, if I can reconfigure it to my needs. In this case I have over 19 million distinct values in the table that would be #T2 in your example. This should be significantly paired down since the customer in this situation is supposed to send the claim numbers they want the info for, then my query is to run and output the all the codes for all the claim numbers they send in. I then stuff it into a delimited file to send back to them.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
July 22, 2010 at 2:56 am
Hi,
Did you tried using T-SQL Pivot syntax in your codes?
Please also check the above url for pivot table samples.
July 22, 2010 at 3:08 am
Sorry, instead of sql pivot please try following code
select
Name, address, phone,
ClaimCode1 = MAX(ClaimCode1),
ClaimCode2 = MAX(ClaimCode2),
ClaimCode3 = MAX(ClaimCode3),
ClaimCode4 = MAX(ClaimCode4),
ClaimCode5 = MAX(ClaimCode5)
from (
select
Name, address, phone,
ClaimCode1 = case when rn = 1 then ClaimCode else NULL end,
ClaimCode2 = case when rn = 2 then ClaimCode else NULL end,
ClaimCode3 = case when rn = 3 then ClaimCode else NULL end,
ClaimCode4 = case when rn = 4 then ClaimCode else NULL end,
ClaimCode5 = case when rn = 5 then ClaimCode else NULL end
from (
select
t1.*, t2.ClaimCode,
rn = ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY t1.ClaimNumber)
from Table1Pivot t1
inner join Table2Pivot t2 on t1.ClaimNumber = t2.ClaimNumber
) t
) t
group by Name, address, phone
July 22, 2010 at 6:43 am
I'm getting close. Turns out the data type for the ClaimCode is VarChar, so the Avg operator will not work. Trying to find a better alternative there.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
July 22, 2010 at 6:49 am
try using min or max. Either one works
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 22, 2010 at 7:29 am
Yeah, did the Max. Now I have found data problems. In order to not return a few millions records I chose a random ID that I had verified had the multiple Claim code records I needed for testing and ran it. Data for that claim is missing from one of the other tables in the join, so I need to track down the data issue before I can get back to this. It did run with no errors though, just no data returned.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
July 23, 2010 at 6:21 am
OK, finally got to run it. works like a champ. THANKS!
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
July 23, 2010 at 11:14 am
Now I'm gonna try that other example, by Eralper, as the pivot has an Achilles heel, it spits back a syntax error if any of the ClaimCode values are null, because, I assume, it no longer has a "dynamic" field name.
Also, the customer added another field, actually a calculated field, based on the ClaimCode.
We'll end up with
Case
When Claimcode is X Then 'Y'
When ClaimCode is Z Then 'W'
End As ClaimCodetype,
ClaimCode
From Table1 etc.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply