SQLTeam.com | Weblogs | Forums

Rows to multiple columns on date and Id basis

sql2008r2

#1

Hi experts !
Help required to make a SQL query which retrieves (sample data ) rows data to multiple columns data based on Names and dates.


#2
declare    @sql    nvarchar(max)

select    @sql    = isnull(@sql + ',', N'SELECT    [Date],') + char(13)
        + N'    [Name]  = MAX(CASE WHEN [Name] = ''' + [Name] + ''' THEN [Name]  END),' + char(13)
        + N'    [Net O] = MAX(CASE WHEN [Name] = ''' + [Name] + ''' THEN [Net O] END),' + char(13)
        + N'    [Net G] = MAX(CASE WHEN [Name] = ''' + [Name] + ''' THEN [Net G] END)'  + char(13)
from    [sample data]
group by [Name]    
order by [Name]    

select    @sql    = @sql
        + N'FROM    [sample data]' + char(13)
        + N'GROUP BY     [Date]'

print    @sql
exec    sp_executesql @sql

#3

Hi Khtan,
It works like a Charm thank you ...
sorry how to make the above to use as a View or SP ?


#4

You can't make it a view. View does not support Dynamic SQL.

You can create a stored procedure for this

CREATE PROCEDURE your_stored_procedure_name
AS
BEGIN

< QUERY HERE >

END

#5

Thank you.