0

I have a table with 5 columns with values 0 or 1 and the last column QuoteStatus is of type VARCHAR.

I want to keep ControlNo and the other 4 columns as is, but the last column I need to pivot and also assign value either 0 or 1.

It can be more than 2 values: Lost or No Action.

So the data looks like this now:

enter image description here

DECLARE @Table1 TABLE 
                (
                    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')

But I want it to look like this:

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Serdia
  • 4,242
  • 22
  • 86
  • 159

2 Answers2

1

We should use dynamic query to achieve the result.

In Dynamic Query :

  1. Table variables are only visible in the scope where they are defined. They're like normal variables. Instead of table variable, I used temp tables.

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,'')

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 ( max(QuoteStatus) for QuoteStatus IN (' + @columns + ') )p '

exec(@finalquery)

select ControlNo,Bound, Declined, Rated,Quoted, case when Lost = 'LOST' then 1 else 0 end as Lost, case when [no action] = 'No Action' then 1 else 0 end as [No Action] from ##a

Subramanian
  • 29
  • 2
  • 10
0

I think what you are looking for is the PIVOT operator and for this problem I think you can get the result you want with something like this:

SELECT *
FROM @Table1 a
PIVOT ( COUNT(QuoteStatus) FOR QuoteStatus IN ( [Lost], [No Action] ) b
Brenton
  • 424
  • 2
  • 11