Need help with pivot expression

[pandatime]

Hi,

I am not too familiar with the pivot expression and I can't seem to find any examples online.

What I'm trying to do shouldn't be hard, just don't know the syntax. I am trying to achieve the output under "REPORT".

RAW DATA:
name date
abc 2010-12-01
abc 2010-12-04
def 2010-12-01
xyz 2010-12-02
xyz 2010-12-05
xyz 2010-12-07

REPORT:
abc 2010-12-01 2010-12-04
def 2010-12-01
xyz 2010-12-02 2010-12-05 2010-12-07

I don't even know if I actually need to use pivot, or if something would be easier.

Thanks much

 

[markros]

select * from (select [Name], [Date], row_number() over (partition by [Name] order by [Date]) as Row from RawData) scr PIVOT (max([Date]) for Row in ([1],[2],[3])) pvt

Assuming there are never more than 3 dates per name and you want to display them. If you don't know how many dates are per name, then you need to do dynamic pivot a similar way.

PluralSight Learning Library

 

[pandatime]

Yes, this works, thanks.

Unfortunately, I didn't really explain clearly what I wanted, sorry:

REPORT:
abc 2010-12-01 NULL NULL 2010-12-04 NULL
def 2010-12-01 NULL NULL NULL NULL
xyz NULL 2010-12-02 NULL NULL 2010-12-05

I need a column for each DAY for the past 5 days or so. If there was no date, then NULL. I figured out a crosstab that does this nicely. Does pivot allow you to do this?

Thanks

 

[markros]

Yes. Dynamic Pivot will allow this (or you can always use 31 even for Feb).

You need a calendar table. Then you do a cross join with all names and then LEFT JOIN the result of it with your table based on the date field. And finally PIVOT the data.

This is if you want to solve this problem in T-SQL. I believe SSRS may offer better alternatives - I suggest to google it and post a link here if you find it.

PluralSight Learning Library