1

I have two tables named Emp and TrainingTable

  • Emp columns (EmpId(PK, int, not null), EmpName(nchar(10), null))
  • TrainingTable columns (EmpId(FK, int, null), TainingId(int, null), TainingName(nvarchar(50), not null))

Code:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(TainingId) 
                from TrainingTable
                group by TainingId
                order by TainingId
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT EmpName,' + @cols + ' from 
         (
            select Emp.EmpName, TainingName, TainingId
            from TrainingTable INNER JOIN Emp ON TrainingTable.EmpId = Emp.EmpId
        ) x
        pivot 
        (
            max(TainingName)
            for TainingId in (' + @cols + ')
        ) p '

execute(@query)

Now what I want to do is to rename the columns 1,2,3.. with Training1,Traning2,.. and so on.. the result table may change depending on data. columns may increase..

I tried almost every thing .. but not getting the correct way to achieve it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sonia
  • 45
  • 1
  • 9

1 Answers1

0

Try this

You need to cast your column Using CAST()

        DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME('Training' + CAST(TainingId AS Varchar(50))) 
                        from TrainingTable
                        group by TainingId
                        order by TainingId
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

        set @query = 'SELECT EmpName,' + @cols + ' from 
                 (
                    select Emp.EmpName, 
                           TainingName,
                           col = ''Training'' + cast(TainingId as varchar(10))
                    from TrainingTable 
                    INNER JOIN Emp ON TrainingTable.EmpId = Emp.EmpId
                ) x
                pivot 
                (
                    max(TainingName)
                    for col in (' + @cols + ')
                ) p '

        execute(@query)

Refer

Community
  • 1
  • 1
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • i got error "Msg 102, Level 15, State 1, Line 14 Incorrect syntax near 'Training'." so i added extra single quote ''Training'' and executed it .. then i got error "Msg 8155, Level 16, State 2, Line 6 No column name was specified for column 3 of 'x'. Msg 207, Level 16, State 1, Line 9 Invalid column name 'TainingId'." – Sonia Jan 13 '14 at 11:12
  • yes i tried it but got "Msg 8155, Level 16, State 2, Line 6 No column name was specified for column 3 of 'x'. Msg 207, Level 16, State 1, Line 9 Invalid column name 'TainingId'" – Sonia Jan 13 '14 at 11:13
  • @Sonia The error is only due to ''Training'' in query. Try with queotes – Vignesh Kumar A Jan 13 '14 at 11:15
  • @Sonia I have Updated solution – Vignesh Kumar A Jan 13 '14 at 11:18
  • Please refer this and http://stackoverflow.com/questions/19408047/sql-complex-dynamic-pivoting-2?rq=1 and make a change. or build your schema in http://www.sqlfiddle.com/ – Vignesh Kumar A Jan 13 '14 at 11:24
  • @VigneshKumar, OP updated your answer with their working code (which I fixed the formatting of), feel free to roll back if there's any problem with that – OGHaza Jan 13 '14 at 12:21