SQL Query help getting rows into multiple columns

Here is my current SQL query

SELECT
tck_adv.PersonIdNo,
tck_adv.FirstName,
tck_adv.LastName,
tck_adv.WithholdingDescription,
tck_adv.CurrentWithholdingAmount

FROM
dbo.tck_adv tck_adv

Where
tck_adv.PaymentDate >= '2018-01-01'

I get back the following:
PersonIdNo FirstName LastName WithholdingDescription Withholding Amount
123456 Bill Smith Deduct 1 100.00
123456 Bill Smith Deduct 2 9000.00
987654 Jane Jones Deduct 1 54.00
987654 Jane Jones Deduct 2 1900.00
564321 Lori Apple Deduct 1 99.00

What I need is it to appear as
PersonIdNo FirstName LastName Deduct 1 Deduct 2
123456 Bill Smith 100.00 9000.00
987654 Jane Jones 54.00 1900.00
564321 Lori Apple 99.00 0.00

How can I write the SQL query to achieve this?

hi

i know this topic is from long time ago

i am practicing my SQL

If it helps
GREAT
:slight_smile:
:slight_smile:

I tried using PIVOT operator

drop create data
use tempdb 

go 


drop table data
go 

create table data
(
PersonIDNo   int NULL,
FirstName varchar(100) NULL, 
LastName varchar(100) NULL,
WithHoldingDesc varchar(100) NULL,
WithHoldingAmt   int NULL
)
go 

insert into data select 123456,'Bill','Smith','Deduct 1', 100.00
insert into data select 123456,'Bill','Smith','Deduct 2', 9000.00
insert into data select 987654,'Jane','Jones','Deduct 1', 54.00
insert into data select 987654,'Jane','Jones','Deduct 2' ,1900.00
insert into data select 564321,'Lori','Apple','Deduct 1' ,99.00
go 

select * from data
go
SQL .. using PIVOT
DECLARE @cols  AS NVARCHAR(max), 
        @query AS NVARCHAR(max); 

SET @cols = Stuff((SELECT DISTINCT ',' + Quotename(c.withholdingdesc) 
                   FROM   data c 
                   FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '' 
            ) 

PRINT @cols 

SET @query = 'SELECT  PersonIDNo,FirstName,LastName,' 
             + @cols 
             + 
' from  (   select PersonIDNo,FirstName,LastName,WithHoldingDesc,WithHoldingAmt from data   ) x                           pivot   ( max(WithHoldingAmt) for WithHoldingDesc in (' 
             + @cols + ') ) p ' 

PRINT @query 

EXECUTE(@query)
Result