pivot and concatenate in sql

Question

i got so many examples for pivot but i need both pivot and concatenate like below. i am very new to sql. Please guide me,i need dynamic pivot in both country names and years.

my table like

Country  | year  | played  |  win | loss
----------------------------------------
India    | 2005  | 40      | 15   |  25  
India    | 2006  | 29      | 10   |  19  
India    | 2007  | 52      | 32   |  20  
China    | 2005  | 100     | 68   |  32  
China    | 2006  | 90      | 60   |  30  
China    | 2007  | 110     | 70   |  40

Now i need the result table like

Country | Stat   | 2005  | 2006 |2007
-----------------------------------
India   | played | 40    | 29   | 52  
        |  win   | 15    | 10   | 30  
        |  loss  | 25    | 19   | 22  
China   | played | 100   |  90  | 110  
        |  win   |  68   |  60  |  70  
        |  loss  |  32   |  30  |  40

Have you tried something yet? Stack Overflow is not a free code writing service. — Tim Biegeleisen, Jun 26 '17 at 11:56
If you are new to SQL, a dynamic pivot is probably not a good place to start. — Gordon Linoff, Jun 26 '17 at 11:58
Here is one of many guides on dynamic pivots: [Dynamic SQL PIVOT](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query). Try it out and update your question if you get stuck. — dd4711, Jun 26 '17 at 12:18

score 4 · Accepted Answer · answered Jun 26 '17 at 12:36

Two quick approaches

1) Static

Select Country=choose(Seq,Country,'','')
      ,Stat
      ,[2005],[2006],[2007]
 From (
        Select A.[Country]
              ,A.[Year]
              ,B.*
         From  YourTable A
         Cross Apply ( values (1,'played',played)
                             ,(2,'win'   ,win)
                             ,(3,'loss'  ,loss)
                     ) B (Seq,Stat,Value)
      ) A
 Pivot (max([Value]) For [Year] in ([2005],[2006],[2007]) ) p

2) Dynamic

Declare @SQL varchar(max) = ''
Select @SQL = Stuff((Select Distinct ','+QuoteName(Year) From YourTable Order By 1 For XML Path('')),1,1,'')
Select @SQL = '
Select Country=choose(Seq,Country,'''','''')
      ,Stat
      ,'+ @SQL +'
 From (
        Select A.[Country]
              ,A.[Year]
              ,B.*
         From  YourTable A
         Cross Apply ( values (1,''played'',played)
                             ,(2,''win''   ,win)
                             ,(3,''loss''  ,loss)
                     ) B (Seq,Stat,Value)

      ) A
 Pivot (max([Value]) For [Year] in (' + @SQL  + ') ) p'
Exec(@SQL);

Both Return

@JohnCappelletti I learned CHOOSE Function from you Thank You — , Jun 26 '17 at 13:04
@Srini131 Easy to overlook CHOOSE. We always think CASE first — John Cappelletti, Jun 26 '17 at 13:06

pivot and concatenate in sql

1 Answers1