CODEWITHSUNDEEP
sqlsql-server-2008pivot
KrisW
KrisW

Reputation: 165

Invalid column name after PIVOT

I would like something like this:

42 | 41 | 31 |  32 | Name
----------------------------    
 O                    42  
     X                41  
                P     32  
     Y                41  
     Z                41

The column headers that are pivoted are also the value in the name column. The various columns can have different statuses. This is what I have but I keep getting an error saying the ValveGroupName column is invalid.

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

select @cols = stuff((select ',' + quotename(ValveGroupName)
                        from dbo.adm_ValveGroup vgroup,
                             dbo.adm_Station station
                       where station.StationID = vgroup.StationID
                         and station.StationName in ('CBRN') 
                       order by vgroup.ValveGroupName desc
                         for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')

set @query = 'select ValveGroupName,' + @cols + ' from
              (
                select vlog.status,
                       vgroup.ValveGroupName                   
                  from dbo.adm_Station station,
                       dbo.adm_ValveGroup vgroup,
                       dbo.valvegroup_log vlog
                 where station.StationID = vgroup.StationID
                   and vgroup.ValveGroupID = vlog.ValveGroupID
                   and station.StationName in (''CBRN'')
                   and vlog.logdate between ''2012-10-01'' and ''2012-10-30''
              ) x
              pivot
              (
                 max(status)
                 for ValveGroupName in (' + @cols + ')
              ) p '

execute (@query)

What's wrong with this query?

Upvotes: 0

Views: 1126

Answers (1)

Taryn
Taryn

Reputation: 247810

Since your ValveGroupName is becoming the new column names from the PIVOT, you normally won't include that in the final select list. Since you are basically "removing" the ValveGroupName to become the new columns, SQL Server doesn't have that column any longer so it throws an error.

The code would normally be:

set @query = 'select ' + @cols + ' 
          from
          (
            select vlog.status,
                   vgroup.ValveGroupName                   
              from dbo.adm_Station station
              inner join dbo.adm_ValveGroup vgroup
                 on vgroup.ValveGroupID = vlog.ValveGroupID
              inner join dbo.valvegroup_log vlog
                 on station.StationID = vgroup.StationID
             where station.StationName in (''CBRN'')
               and vlog.logdate between ''2012-10-01'' and ''2012-10-30''
          ) x
          pivot
          (
             max(status)
             for ValveGroupName in (' + @cols + ')
          ) p '

However, since you are aggregating a string that will return a single row for each ValveGroupName, if you want to return multiple rows, then you'll need to include a row_number:

set @query = 'select ' + @cols + ' 
          from
          (
            select vlog.status,
                   vgroup.ValveGroupName,
                   seq = row_number() over(partition by vgroup.ValveGroupName               
                                             order by vlog.status)
              from dbo.adm_Station station,
              inner join dbo.adm_ValveGroup vgroup
                 on vgroup.ValveGroupID = vlog.ValveGroupID
              inner join dbo.valvegroup_log vlog
                 on station.StationID = vgroup.StationID
             where station.StationName in (''CBRN'')
               and vlog.logdate between ''2012-10-01'' and ''2012-10-30''
          ) x
          pivot
          (
             max(status)
             for ValveGroupName in (' + @cols + ')
          ) p '

This change will allow you to return multiple rows in each column. Note: I also change the code to use INNER JOIN syntax instead of the comma joins, with the conditions in the WHERE clause.

Upvotes: 2

Related Questions