SQL Server PIVOT multiple dates

Question

Hi I have my data in a table like this:

Herd | Tag | Byr | Mob | Date        | Trait | Value
-----|-----|-----|-----|-------------|-------|-------
6002 | 1/08| 2008| 1   | 2015-08-17  | LWT   | 425
6002 | 1/08| 2008| 3   | 2015-12-22  | LWT   | 516
6002 | 1/08| 2008| 4   | 2016-04-06  | LWT   | 688

I need it changed from Long to wide format using the PIVOT function

Herd | Tag | Byr | Mob | 2015-08-17 | Mob | 2015-12-22 | Mob | 2016-04-06
-----|-----|-----|-----|------------|-----|------------|-----|------------
6002 | 1/08| 2008| 1   | 425        |  3  |  516       |  4  |    688

Any help would be greatly appreciated

I also have multiple Dates (hundreds.)

proctor
– proctor

2017-01-25 22:40:45 +00:00
Commented Jan 25, 2017 at 22:40 — proctor
– proctor, Commented Jan 25, 2017 at 22:40

John Cappelletti · Accepted Answer · 2017-01-26 00:43:35Z

1

Here is a Dynamic Pivot for multiple columns

Declare @SQL varchar(max) 
Set @SQL = Stuff((Select  Distinct ',' +QuoteName(concat('Mob_',Date))+' as Mob,'+QuoteName(Date)
                   From   Yourtable 
                   Order by 1 
                   For XML Path('')),1,1,'') 

Select  @SQL = '
Select [Herd],[Tag],[Byr],' + @SQL + '
From (
        Select [Herd],[Tag],[Byr]
              ,B.* 
        From  YourTable A
        Cross Apply (
                     Values (concat(''Mob_'',A.Date),cast(A.Mob as nvarchar(50)))
                           ,(concat('''',A.Date)    ,cast(A.Value as nvarchar(50)))
                    ) B (Item,Value)
     ) A
Pivot (max([Value]) For [Item] in (' + Replace(@SQL,' as Mob','') + ') ) p'

Exec(@SQL);

Returns

EDIT

Updated for 2014 ... Concat() and Values

EDIT 2:

Another option to casting a nvarchar(50), you could use format(). For example:

             Values (concat(''Mob_'',A.Date),Format(A.Mob,''0''))
                   ,(concat('''',A.Date)    ,Format(cast(A.Value as decimal(18,2)),''#,##0.00''))
            ) B (Item,Value)

edited Jan 26, 2017 at 0:43

answered Jan 25, 2017 at 23:15

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

proctor Over a year ago

So my table is called "Trait" so I need to replace YourTable with trait?

John Cappelletti Over a year ago

@proctor Just to be clear, are you using the code listed above? ie not a previous version

proctor Over a year ago

Yes Ive copied your code - I'm running SQL Server 2014

John Cappelletti Over a year ago

@proctor you tagged 2008. I would have used concat() and values rather than Union All. Do you want the change?

proctor Over a year ago

Yeah sorry John - if you could please

|

Collectives™ on Stack Overflow

SQL Server PIVOT multiple dates

1 Answer 1

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related