Sql pivot issues

Transact-SQL
1

I Have a table (CERTS) with the folling columns

ID, NAME, STATUS.I can can have 1 id with multiple types and status

example ID NAME STATUS
412541 CAE cERTIFIED
412541 CSE APPROVED
412541 CAP null
412541 CDA INACTIVE

Now i would like to represent all id's in on single row eample

ID status_cae status_cse status_cap status_cda
412541 certified approved null INACTIVE

so i create a pivot table

create table #temp
(id varchar (200) null,
status_cae varchar (200) null,
status_cse varchar (200) null,
status_cap varchar (200) null,
status_cda varchar (200) null,)

insert into #temp

SELECT ID,[1] AS status_cae, [2] AS status_cse, [3] AS status_cap, [4] AS status_cda
from
(select id,type,
row_number()over (partition BY id order by id) as rownum
FROM CERTS)a
pivot (max(type) from rownum in ([1] , [2], [3], [4]) as pvt

however when i pivot this it returns

ID status_cae status_cse status_cap statuse_cda
412541 certified INACTIVE approved null

what am i doing wrong in my pivot and how do i get it to pivot correctly, i tried to change to oder of the row number which works
however when i retrieve another ID the sort order is out of order again, Please assist

2

WOuld you please post the results you expect from your query using the sample data?

3

The results should be in this order

ID status_cae status_cse status_cap status_cda
412541 certified approved null INACTIVE

NOT

ID status_cae status_cse status_cap statuse_cda
412541 certified INACTIVE approved null

Based upon my pivot but its not , as stated I can move around the
SELECT ID,[1] AS status_cae, [2] AS status_cse, [3] AS status_cap, [4] AS status_cda

to

SELECT ID,[1] AS status_cae, [2] AS status_cse, [4] AS status_cap, [3] AS status_cda

and it works but affects other id' s wit different names

4

Where is the column 'type'? I don't see it in your sample data (columns ID, Name, Status)

5

sorry about that TYPE should be NAME, I updated the column headings

6

SELECT ID,[1] AS status_cae, [2] AS status_cse, [3] AS status_cap, [4] AS status_cda
from
(select id,name,
row_number()over (partition BY id order by id) as rownum
FROM CERTS)a
pivot (max(type) from rownum in ([1] , [2], [3], [4]) as pvt

7

Something like this:

declare @CERTS table (id int, name varchar(20), status varchar(20))
insert into @CERTS(id, name, status) values

--example ID NAME STATUS
(412541, 'CAE', 'cERTIFIED'),
(412541, 'CSE', 'APPROVED' ),
(412541, 'CAP', null       ),
(412541, 'CDA', 'INACTIVE' )

SELECT ID,[1] AS status_cae, [2] AS status_cse, [3] AS status_cap, [4] AS status_cda
from
(select id,status,
row_number()over (partition BY id order by id) as rownum
FROM @CERTS)a
pivot (max(status) for rownum in ([1] , [2], [3], [4])) as pvt
8

ok don't get that, the reason I created a tmp table #tmpDelta is so that I can rename the column to
id status_cae status_cse status_cap status_cda
instead of the original ID NAME STATUS

with your result you are inserting into @CERTS instead of my temp table #tmpDelta
the reason I did a temp table is because after my pivot is completed I then have and additional step

that does a INSERTS

INTO NEW_TABLE

(vID,
vstatus_cae ,
vstatus_cse ,
vstatus_cap ,
vstatus_cda )

select

ID,
status_cae ,
status_cse ,
status_cap ,
status_cda ,
from
FROM #tmpDelta tmp
WHERE NOT (tmp.id in (select id from NEW_TABLE))

then drop tmpDelta

I cant do a insert with @CERTS

9

Well, that's fine, use whatever table you want. that part is not important. I use table variables for testing because they are convenient and leave nothing to clean up, that's all

10

K but that is what my original question has how is what you provided different from what i did

thanks

11

You are partitioning and ordering by id, but there are multiple rows per id. With each partition, the ordering of the rows will be in-determinant. Perhaps you want:

row_number()over (partition BY id order by name) as rownum
12

my results are still pivoting incorrectly

instead of

ID status_cae status_cse status_cap status_cda
412541 certified approved null INACTIVE

its pivoting like

ID status_cae status_cse status_cap statuse_cda
412541 certified INACTIVE approved nul

13

that's because

status_cae status_cse status_cap statuse_cda

are not in alphabetical order. It should be

status_cae status_cap status_cda status_cse

the order of the aliases needs to match the order of the statuses in the rows.

14

The problem I am having is that not all the statuses are in order so if i sort by alphabetical order its going to give incorrect results, is it possible to pivot without sorting.
so some id's may appear in the table as for lets say ID 412541
status_cae ,
status_cse ,
status_cap ,
status_cda ,

or
ID 7541254 =
status_cse ,
status_cae ,
status_cap ,
status_cda ,

or
visa versa so if I sort alphabetically it give incorrect results

15

Well, you did pivot without sorting, but you didn't like the results. Which is not too surprising since left to itself, SQL orders data as it sees fit which often does not fit the business requirements.

16

how can I rewrite this so that it returns the correct values as per business requirements instead of ordering as it sees fit

17

You have add an ORDER BY clause that fits your business rules

18

Can't you just write:

declare @CERTS table (id int, name varchar(20), status varchar(20))
insert into @CERTS(id, name, status) values

--example ID NAME STATUS
(412541, 'CAE', 'cERTIFIED'),
(412541, 'CSE', 'APPROVED' ),
(412541, 'CAP', null       ),
(412541, 'CDA', 'INACTIVE' )

select id, cae, cse, cap, cda
from
(
select id, name, status from @certs
) src
pivot
(
max(status) for name in (cae, cse, cap, cda)
) pvt
[/quote]