CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

Using pivot in SQL Server not returning desired output

I have two tables like this:

**tblTagDescription**

enter image description here

and  **tblDataLog**

enter image description here

Now I want to show record of any specific group and in one there might be same group for multiple id in tbltagdescription. And id of tbltagdescription is foreign key for tblDataLog as TagDescID.

Here 'Group1' has 10 ID as from 1 to 10. and there might be multiple record for these ID (from 1 to 10) in tbldatalog. I want these ID from 1 to as columns. For this I used pivot:

DECLARE @COlsID NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Group NVARCHAR(50) = 'Group1'

IF OBJECT_ID('tempdb..##MYTABLE') IS NOT NULL 
    DROP TABLE ##MYTABLE

SELECT 
    @COlsID = COALESCE(@ColsID + '],[','') + CONVERT(NVARCHAR(5), z.TagDescID)  
FROM
    (SELECT DISTINCT TOP 50 tblDataLog.TagDescID 
     FROM tblDataLog 
     INNER JOIN tblTagDescription ON tblDataLog.TagDescID = tblTagDescription.ID 
     ORDER BY tblDataLog.TagDescID) z  

SET @COlsID='[' + @COlsID + ']'

SET @SQL='select [DATE],SHIFT, ' + @COlsID + ' into ##MYTABLE from ( select [Date], Value, 
                     (CASE  
                                WHEN ((DATEPART(hour,[DATE]))>6 and (DATEPART(hour,[DATE]))<14) THEN ''A'' 
                                WHEN ((DATEPART(hour,[DATE]))>=14 and (DATEPART(hour,[DATE]))<22) THEN ''B'' 
                                WHEN ((DATEPART(hour,[DATE]))>=22 or (DATEPART(hour,[DATE]))<6) THEN ''C'' 
                            END )AS SHIFT 

                     from tblDataLog )d pivot(max(Value) for TagDescID in (' + @COlsID + ')) piv;'

        EXEC (@SQL)

Now when I execute this statement, I get an error:

Invalid column name 'TagDescID'

but there is this column in tbldatalog. How to solve this query?

Upvotes: 0

Views: 51

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You need TagDescID column in subquery.

DECLARE @COlsID NVARCHAR(MAX) = ''
DECLARE @COlsAlias NVARCHAR(MAX) = ''
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Group NVARCHAR(50) = 'Group1'

SELECT 
   @COlsID = @ColsID + ',' + z.TagDescID, 
   @COlsAlias = @COlsAlias + ',' + z.TagDescID + ' AS ' + z.ReportTag   
FROM
    (SELECT DISTINCT TOP 50 tblDataLog.TagDescID ID,  QUOTENAME(CONVERT(NVARCHAR(5), tblDataLog.TagDescID )) TagDescID, QUOTENAME(tblTagDescription.ReportTag) ReportTag 
     FROM tblDataLog 
        INNER JOIN tblTagDescription ON tblDataLog.TagDescID = tblTagDescription.ID 
     ORDER BY tblDataLog.TagDescID 
     ) z

SET @COlsID= STUFF(@COlsID,1,1,'')
SET @COlsAlias= STUFF(@COlsAlias,1,1,'')

SET @SQL='select [DATE],SHIFT, ' + @COlsAlias + ' into ##MYTABLE from ( select [Date], Value, TagDescID,
                     (CASE  
                                WHEN ((DATEPART(hour,[DATE]))>6 and (DATEPART(hour,[DATE]))<14) THEN ''A'' 
                                WHEN ((DATEPART(hour,[DATE]))>=14 and (DATEPART(hour,[DATE]))<22) THEN ''B'' 
                                WHEN ((DATEPART(hour,[DATE]))>=22 or (DATEPART(hour,[DATE]))<6) THEN ''C'' 
                            END )AS SHIFT 

                     from tblDataLog )d pivot(max(Value) for TagDescID in (' + @COlsID + ')) piv;'

EXEC (@SQL)

Upvotes: 1

Related Questions