August 14, 2013 at 1:24 pm
GTJohnson73 (8/14/2013)
Yes. That is exactly what is needed.
I am pretty swamped at the moment. What you need here is called a dynamic cross tab. You might try checking out the articles in my signature about cross tabs. There are lots of other resources around that should have examples of how to do this sort of thing.
If nobody else comes along to help I will try to get back to this in the next day or two.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 14, 2013 at 3:09 pm
I did some more digging and got it to work, but not after going through the scenic route... I had to number the rows for each pyEventID. So if pyEventID (ABC) had 3 rows then it was labeled 1,2,3. If pyEventID (XYZ) had 2 rows then it was labeled 1,2. This was accomplished by the following SQL Code:
(ROW_NUMBER() OVER (ORDER BY pyEventID) - RANK ()OVER (ORDER BY pyEventID) + 1) AS Int_ID
Results from above code...
Int_IDpyEventIDProviderNameProviderNumber
1ABC-I-10942683Company 1432782
2ABC-I-10942683Company 21089213
3ABC-I-10942683Company 31099347
4ABC-I-10942683Company 41178255
5ABC-I-10942683Company 51255608
6ABC-I-10942683Company 61303867
1ABC-I-23942546Company A1326624
2ABC-I-23942546Company B10006409
3ABC-I-23942546Company C10017844
4ABC-I-23942546Company D10019170
5ABC-I-23942546Company E10027239
6ABC-I-23942546Company F10029259
7ABC-I-23942546Company G10066360
I am posting in case someone else may need to know how to repeat the row count for each individual ID (interaction).
Then from there I used your 2nd half of the code from the CTE used in this post to create the single row per inaction with the Provider name and number going across.
select PNC.pyEventID
,MAX(case when PNC.Int_ID = 1 then PNC.ProviderName ELSE '' end) as ProviderName_1
,MAX(CASE WHEN PNC.Int_ID = 1 then PNC.ProviderNumber ELSE '' END) AS ProviderNum_1
,MAX(case when PNC.Int_ID = 2 then PNC.ProviderName ELSE '' end) as ProviderName_2
,MAX(CASE WHEN PNC.Int_ID = 2 then PNC.ProviderNumber ELSE '' END) AS ProviderNum_2
,MAX(case when PNC.Int_ID = 3 then PNC.ProviderName ELSE '' end) as ProviderName_3
,MAX(CASE WHEN PNC.Int_ID = 3 then PNC.ProviderNumber ELSE '' END) AS ProviderNum_3
,MAX(case when PNC.Int_ID = 4 then PNC.ProviderName ELSE '' end) as ProviderName_4
,MAX(CASE WHEN PNC.Int_ID = 4 then PNC.ProviderNumber ELSE '' END) AS ProviderNum_4
INTO #INT_SL
from #PN_Count PNC
group by PNC.pyEventID
Thank you for your time and patience with this request. I really do appreciate it.
August 14, 2013 at 3:23 pm
You are welcome. I am glad you got it working to suit your needs. I guess at the end of it you went with a static cross tab instead of the dynamic version.
Thanks for letting me know you got it working. Have a good one.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply