CODEWITHSUNDEEP
sqlpivot
SRN
SRN

Reputation: 59

On SQL Pivoting , how to get single column with comma sepated data

Below is my pivot query.

    Select MID, [1] as AthleteName1st, 
[2] as AthleteName2nd, 
[3] as AthleteName3rdPoint
from(
select MId, AthleteId , Position,
(row_number() over(partition by Position ,R.EventCode,MId  order by Position)) rn
from L_Results R 
where R.EventCode = '040' and R.Season = (select Season from L_SeasonTable) and Position in (1,2,3) 
) src
pivot
(
  max([AthleteId])
  for Position in ([1] ,[2] , [3]) 
) piv

result I am getting: 

MID     AthleteName1st  AthleteName2nd  AthleteName3rdPoint
65      14231808        14201894        14202005
122     14238588        14238562        14186000
178     14238588        14231808        14201894
234     14201847        14201894        14367591
346     14201847        14202176        14186000
374     14201847        14202176        14201912
402     14202176        14249856        14201894
402     14201848        NULL            NULL

result required as: 

MID     AthleteName1st  AthleteName2nd  AthleteName3rdPoint
65      14231808        14201894        14202005
122     14238588        14238562        14186000
178     14238588        14231808        14201894
234     14201847        14201894        14367591
346     14201847        14202176        14186000
374     14201847        14202176        14201912
402     14202176,       14249856        14201894
        14201848

As in pivot query , I am retrieving data from L_Results table for only 1st, 2nd and 3rd position, in L_Results table for MID '402' ,for 1st position I have 2 records, i.e why I am getting 2 records in results for MID '402'.. In the above case I need to get result as shown above, only one record for MID '402'.

I really appreciate the help... thanks

Upvotes: 0

Views: 194

Answers (1)

GarethD
GarethD

Reputation: 69759

You are getting multiple rows because of the column rn in your subquery src, you never use this column, but because it exists in the source it is used in the implied group by of the pivot. So for Mid = 2 both athlete 14202176 and 14201848 are have a position = 1.

You need to get your data in a format where it is one record per Mid/Position combination, and you can do this using the XML extensions of sql-server to concatenate your athlete IDs into a comma separated string. You can then PIVOT them into the required format:

WITH Data AS
(   SELECT  MId, 
            AthleteId, 
            Position
    FROM    L_Results R 
    WHERE   R.EventCode = '040' 
    AND     R.Season = (SELECT Season FROM L_SeasonTable) 
    AND     Position IN (1, 2 ,3) 
), PivotData AS
(   SELECT  t.Mid,
            t.Position,
            Athletes = STUFF((  SELECT  ',' + CAST(AthleteID AS VARCHAR)
                                FROM    Data t2
                                WHERE   t.Mid = t2.Mid
                                AND     t.Position = t2.Position
                                FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    FROM    (   SELECT  DISTINCT Mid, Position 
                FROM    Data
            ) t
)
SELECT  MID, 
        AthleteName1st = [1], 
        AthleteName2nd = [2], 
        AthleteName3rdPoint = [3]
FROM    PivotData
        PIVOT 
        (   MAX(Athletes)
            FOR Position IN ([1], [2], [3])
        ) pvt;

Upvotes: 1

Related Questions