-1

Hi I have a table like below,.

Stat                        Points  TeamA TeamB     Date
Highest Total               248     5       387     2016-05-14
Lowest Total                153     2       5       2016-05-11
Highest Successful Chase    195     5       386     2016-05-07
Lowest Score Defended       211     5       4       2016-05-18

I want the result like,

        Highest Total   Lowest Total    Highest Chase   Low Defended
  A     248             153             195             211     
  B     5               2               5               5   
  C     384             5               386             4   
  D     2016-05-14      2016-05-11      2016-05-07      2016-05-18

Help me to get the result, I'm newer one for the sql server.

DineshDB
  • 5,998
  • 7
  • 33
  • 49
  • The first table version is horrible, the second slightly better... Can't really understand any of them... – jarlh May 23 '16 at 07:34
  • The first line of the First table is column name of it's. – DineshDB May 23 '16 at 07:36
  • 2
    Possible duplicate of [Simple way to transpose columns and rows in Sql?](http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) – blackbishop May 23 '16 at 07:42
  • 1
    Can you not perform this rotation in a presentation layer, e.g. an application or report builder? You desired result has columns containing data of different types - which in practice means it's either not going to happen in SQL or you've got to destroy your type information and make everything strings. – Damien_The_Unbeliever May 23 '16 at 08:00
  • There is a variant data type. Never used it but it exists. – Paul Maxwell May 23 '16 at 08:30

1 Answers1

1

Pivot and unpivot, whatever this 'A', 'B', .. really mean. It also shows casting different types to common varchar(20). You may need different cast according to your requierments.

select rowType,
  max(case ut.stat when 'Highest Total' then value end) as [Highest Total],
  max(case ut.stat when 'Lowest Total' then value end) as [Lowest Total],
  max(case ut.stat when 'Highest Chase' then value end) as [Highest Chase],
  max(case ut.stat when 'Lowest Score Defended' then value end) as [Lowest Defended]
from t
cross apply (
    select * 
    from (
        values 
        ('A', cast(points as varchar(20)), stat),
        ('B', cast(TeamA as varchar(20)), stat),        
        ('C', cast(TeamB as varchar(20)), stat),
        ('D', cast(Date as varchar(20)), stat)
     ) ut(rowType, value, stat) )ut
group by rowType
Serg
  • 22,285
  • 5
  • 21
  • 48