CODEWITHSUNDEEP
sql-servert-sql
Ali Ahmadi
Ali Ahmadi

Reputation: 2417

Convert Tsql query result from rows to column names with other rows as column data

I use this query to get result :

Declare @Mag Int = 237006
;With Res
As
(
  Select
    Mnf.FileTypeID As MagType,
    Mnf.InsertDate As MagDate,
    Mnf.Star,
    Nl.LogType,
    Nl.InsertDate As LogDate
  From 
    MagazineNumberFiles As Mnf
    Left Outer Join Nomags_Log As Nl On Nl.MagazineNumberID = Mnf.MagazineNumberID
  Where 
    Mnf.MagazineNumberID = @Mag
)
Select * From Res

Result of this query is:

MagType MagDate         Star    LogType LogDate
2           2013-04-13  *       7       2013-04-16 
6           2013-04-14  *       3       2013-04-17 
8           2013-04-15  NULL    NULL    NULL

I want convert this result to :

MagType2    MagStar2 MagType6   MagStar6 MagType8 MagStar8 LogType7     LogType3
2013-04-13  *        2013-04-14 *        5        NULL     2013-04-16   2013-04-17

How can I do this using tsql ?

Upvotes: 2

Views: 678

Answers (1)

Alexey A.
Alexey A.

Reputation: 902

You can, with some ugly dynamic sql: 

declare @sql nvarchar(max) = '';

select @sql = 'select ' + stuff((select
',''' + convert(varchar,MagDate) + ''' as MagType' + convert(varchar,MagType)
+ char(10)
+ isnull(',''' + convert(varchar,Star) + '''', ',null') + ' as MagStar' 
+ convert(varchar,MagType)
+ char(10)
+ isnull(',''' + convert(varchar,LogDate) + ''' as LogType', '') 
+ isnull(convert(varchar,LogType), '')
+ char(10)
from Res
for xml path(''))
,1,1,'');

exec sp_executesql @sql;

Upvotes: 1

Related Questions