Getting error when dynamically unpivoting in SQL Server 2012

Question

The goal is to dynamically pivot data and then dynamically unpivot it.

I am getting an error saying Invalid ControlNo

I am assuming its because variable @colsUnpivot contains ControlNo column.

But how can I fix it? I definitely need that ControlNo.

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

Create table #Table1 ( ControlNo INT, Bound INT, Declined INT, Rated INT, Quoted INT, QuoteStatus VARCHAR(50) )

INSERT INTO #Table1 (ControlNo, Bound, Declined, Rated, Quoted, QuoteStatus) 
    VALUES  (1111,1,0,1,1,'Lost'), 
            (2222,0,1,0,1,'No Action'), 
            (3333,1,1,0,0,NULL), 
            (4444,1,0,0,1,'Lost'), 
            (5555,0,1,1,1,'No Action')

DECLARE @columns AS NVARCHAR(MAX), 
        @finalquery AS NVARCHAR(MAX);

SET @columns = STUFF((SELECT distinct ',' + QUOTENAME(QuoteStatus) FROM #Table1 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')
--PRINT @columns

set @finalquery = '
        select  p.controlno,
                p.Bound, 
                p.Declined, 
                p.Rated,
                p.Quoted,' + @columns + '
        into ##A 
        from ( select ControlNo, Bound, Declined, Rated, Quoted, QuoteStatus
                from #Table1
            )a 
    pivot 
    ( 
     COUNT(QuoteStatus) 
     for QuoteStatus IN (' + @columns + ') 
    )p '

exec(@finalquery)

--SELECT * 
--FROM ##a


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

select @colsUnpivot 
  = stuff((select ','+quotename(C.name)
           FROM tempdb.sys.columns c
           WHERE c.object_id = OBJECT_ID('tempdb..##A') 
           for xml path('')), 1, 1, '')
--PRINT @colsUnpivot

set @query 
  = 'select ControlNo, Counts, Status
     from ##A
     unpivot
     (
        Counts
        for Status in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

Output should look like this:

Answer 1

I assume your sample output was that, a sample, and you excluded the other control numbers. If so, changing the where clause in your stuff will remove that column from the unpivot group, and give you desired results. You can't return a column that you are pivoting or unpivoting.

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

Create table #Table1 ( ControlNo INT, Bound INT, Declined INT, Rated INT, Quoted INT, QuoteStatus VARCHAR(50) )

INSERT INTO #Table1 (ControlNo, Bound, Declined, Rated, Quoted, QuoteStatus) 
    VALUES  (1111,1,0,1,1,'Lost'), 
            (2222,0,1,0,1,'No Action'), 
            (3333,1,1,0,0,NULL), 
            (4444,1,0,0,1,'Lost'), 
            (5555,0,1,1,1,'No Action')

DECLARE @columns AS NVARCHAR(MAX), 
        @finalquery AS NVARCHAR(MAX);

SET @columns = STUFF((SELECT distinct ',' + QUOTENAME(QuoteStatus) FROM #Table1 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')
--PRINT @columns

set @finalquery = '
        select  p.controlno,
                p.Bound, 
                p.Declined, 
                p.Rated,
                p.Quoted,' + @columns + '
        into ##A 
        from ( select *
                from #Table1
            )a 
    pivot 
    ( 
     COUNT(QuoteStatus) 
     for QuoteStatus IN (' + @columns + ') 
    )p '

exec(@finalquery)

--SELECT * 
--FROM ##a


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

select @colsUnpivot 
  = stuff((select ','+quotename(C.name)
           FROM tempdb.sys.columns c
           --notice i remove controlno here...
           WHERE c.object_id = OBJECT_ID('tempdb..##A') and c.name <> 'controlno'
           for xml path('')), 1, 1, '')
--PRINT @colsUnpivot


set @query 
  = 'select  Controlno, Counts, Status
     from ##A
     unpivot
     (
        Counts
        for Status in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

To get your exact output, then just add a where clause to the set @query.

set @query 
  = 'select  Controlno, Counts, Status
     from ##A
     unpivot
     (
        Counts
        for Status in ('+ @colsunpivot +')
     ) u
     where controlno = 1111'