SQLTeam.com | Weblogs | Forums

COnvert columns to row

#1

HI ,

I am trying to convert column benefit_ID to rows but its not working for me, whats the best way to do this? Thanks. I need to do this for every empID

image

0 Likes

#2

Can you provide sample data and expected results? Did you try pivot?

0 Likes

#3

hope this helps:

|Co|EmpID|Benfit ID|
|10|1234|Basic AD&D|
|10|1234|Basic Life|
|10|1234|Basic LTD|
|10|1234|BASICADD|
|10|1234|BASICADDCREDIT|
|10|1234|BASICLIFE|
|10|1234|BASICLIFECREDIT|
|10|1234|Dental|
|10|1234|Dependent Care FSA|
|10|1234|EAP|
|10|1234|Employee Assistance Program|
|10|1234|Health Care FSA|
|10|1234|Health Savings Account|
|10|1234|HSA Reporting|
|10|1234|Limited Purpose Health Care FSA|
|10|1234|LTD|
|10|1234|LTDCREDIT|
|10|1234|Medical|
|10|1234|Optional AD&D|
|10|1234|OPTIONALADD|
|10|1234|Special purpose Prescription Drug|
|10|1234|TRANSPORTATION|
|10|1234|TRIP Parking|
|10|1234|TRIP Transportation|
|10|1234|VACATIONBUY|
|10|1234|VACATIONSELL|
|10|1234|Vision|

0 Likes

#4

please provide sample data as follows

create table sample(Co int, EmpID int, [Benfit ID] varchar(50)

insert into sample
select 10, 1234, 'Basic AD&D' union
select 10, 1234, 'Basic Life'

etc

0 Likes

#5

This is part of a sub query. the query itself is very big and tbl construct is big as well.

0 Likes

#6

I get that but you provided sample data with pipe delimited. people have to copy that and change it into something else to test it out locally on their server. you can help us help you by doing the leg work and provide sample data in the form I mentioned above.

0 Likes

#7

It wont let me upload xlsx file it says has to be .png, jpg??

0 Likes

#8

please follow this example?

create table sample(Co int, EmpID int, [Benfit ID] varchar(50)

insert into sample
select 10, 1234, 'Basic AD&D' union
select 10, 1234, 'Basic Life'
0 Likes

#9

HI

i tried this

hope it helps
:slight_smile:
:slight_smile:

drop create data
drop table data
go

create table data
(
 Co int null,
 EmpID int null ,
 BenfitID varchar(100) null
 )
 go

insert into data select  10, 1234, 'Basic Life'
insert into data select  10, 1234, 'Basic LTD'
insert into data select  10, 1234, 'BASICADD'
insert into data select  10, 1234, 'BASICADDCREDIT'
insert into data select  10, 1234, 'BASICLIFE'
insert into data select  10, 1234, 'BASICLIFECREDIT'
insert into data select  10, 1234, 'Dental'
insert into data select  10, 1234, 'Dependent Care FSA'
insert into data select  10, 1234, 'EAP'
insert into data select  10, 1234, 'Employee Assistance Program'
insert into data select  10, 1234, 'Health Care FSA'
insert into data select  10, 1234, 'Health Savings Account'
insert into data select  10, 1234, 'HSA Reporting'
insert into data select  10, 1234, 'Limited Purpose Health Care FSA'
insert into data select  10, 1234, 'LTD'
insert into data select  10, 1234, 'LTDCREDIT'
insert into data select  10, 1234, 'Medical'
insert into data select  10, 1234, 'Optional AD&D'
insert into data select  10, 1234, 'OPTIONALADD'
insert into data select  10, 1234, 'Special purpose Prescription Drug'
insert into data select  10, 1234, 'TRANSPORTATION'
insert into data select  10, 1234, 'TRIP Parking'
insert into data select  10, 1234, 'TRIP Transportation'
insert into data select  10, 1234, 'VACATIONBUY'
insert into data select  10, 1234, 'VACATIONSELL'
insert into data select  10, 1234, 'Vision'
go

select * from data 
go
SQL ....
SELECT SS.Co,
       SS.EMPID,
       STUFF(
       (
           SELECT '; ' + BenfitID 
           FROM DATA US
           WHERE US.Co = SS.Co AND US.EMPID = SS.EMPID                 
           FOR XML PATH('')
       ),
       1,
       1,
       ''
            ) [COLUMN TO ROW]
FROM DATA SS
GROUP BY SS.Co,
         SS.EMPID
ORDER BY 1;
Result

0 Likes

#10

Thanks harishgg1!.. I'll try it out.

0 Likes

#11

SELECT Co,EmpID,
( SELECT BenfitID + ','
FROM data p2
WHERE p2.EmpID = p1.EmpID and p2.Co=p1.Co
ORDER BY BenfitID
FOR XML PATH('') ) AS Benefits
FROM data p1
GROUP BY Co,EmpID ;

0 Likes

#12

Thanks mannesravya will try this seems easier .
Pasi

0 Likes

#13

HI Harishgg your query gives me comma separated but I am looking for PIvot table results should be like this:
Thanks. Pasi.
image

0 Likes

#14
use sqlteam
go

create table sample(Co int, EmpID int, [Benfit ID] varchar(50))

insert into sample
select 10, 1234, 'Basic AD and D' union
select 10, 1234, 'Basic Life' UNION
SELECT 10, 1235, 'BASICADD'

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
	@Piv as NVARCHAR(MAX)

SELECT  @cols = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[' + a.[Benfit ID]  + '], '
        FROM (  SELECT  DISTINCT [Benfit ID]
                  FROM sample r ) a
        ORDER BY a.[Benfit ID]
        FOR XML PATH ( '' ) ) b ( Piv );

print @cols

set @query = 'SELECT EmpID, ' + @cols + ' from 
            (
                select [Benfit ID],
				EmpID
				
                from sample
           ) x
            pivot 
            (
                 max([Benfit ID])
                 for [Benfit ID] in (' + @cols + ')
            ) p '

select @query

execute(@query)
drop table sample
0 Likes

#15

Thanks Yosiasz.. I;ll try to make sense out of it. :slight_smile:

0 Likes

#16

hi pasi

i tried it

does this look okay ???

drop create data ..
-- create tables 
create table data
(
 Co int null,
 EmpID int null ,
 BenfitID varchar(100) null
 )
 go

/******************************************************************************/
-- insert data tables 
insert into data select  10, 1234, 'Basic Life'
insert into data select  10, 1234, 'Basic LTD'
insert into data select  10, 1234, 'BASICADD'
insert into data select  10, 1234, 'BASICADDCREDIT'
insert into data select  10, 1234, 'BASICLIFE'
insert into data select  10, 1234, 'BASICLIFECREDIT'
insert into data select  10, 1234, 'Dental'
insert into data select  10, 1234, 'Dependent Care FSA'
insert into data select  10, 1234, 'EAP'
insert into data select  10, 1234, 'Employee Assistance Program'
insert into data select  10, 1234, 'Health Care FSA'
insert into data select  10, 1234, 'Health Savings Account'
insert into data select  10, 1234, 'HSA Reporting'
insert into data select  10, 1234, 'Limited Purpose Health Care FSA'
insert into data select  10, 1234, 'LTD'
insert into data select  10, 1234, 'LTDCREDIT'
insert into data select  10, 1234, 'Medical'
insert into data select  10, 1234, 'Optional AD&D'
insert into data select  10, 1234, 'OPTIONALADD'
insert into data select  10, 1234, 'Special purpose Prescription Drug'
insert into data select  10, 1234, 'TRANSPORTATION'
insert into data select  10, 1234, 'TRIP Parking'
insert into data select  10, 1234, 'TRIP Transportation'
insert into data select  10, 1234, 'VACATIONBUY'
insert into data select  10, 1234, 'VACATIONSELL'
insert into data select  10, 1234, 'Vision'
go
Dynamic Pivot .. SQL ..
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.BenfitID) 
            FROM data c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

---print @cols 

set @query = 'SELECT  Co , EmpID, ' + @cols + ' from 
            (
                select Co
                    , EmpID
                    , BenfitID
                from data
           ) x
            pivot 
            (
                 max(BenfitID)
                for BenfitID in (' + @cols + ')
            ) p '


execute(@query)
Result

0 Likes

#17

Thanks I tried it it looks ok.. need to add other part of query to it.
Pasi

0 Likes

#18

yosiasz, is this code needs be saved as a function , how can I insert this into my main query, The SELECT PART, after DECLARE?
Thanks!

0 Likes

#19

show us your main query. I would put it there.

0 Likes

#20

here you go:

SELECT distinct

A.COMPANY
,B.EMP_ID
, P.NAME
,P.BIRTHDATE
,, P. City
,A.STD_HOURS

,DC.DEP_LAST_NAME
,DC.DEP_BIRTH_DT
,B.COV_LVL

FROM Elections B

left JOIN COVERAGE DC on DC.emp_ID =B.emp_ID

  JOIN PERSON_DATA P  ON P.EMPLID = B.emp_ID 
      join JOB   A  on B.emp_ID = A.emplid 
     JOIN   EMPLOYMENT    E   ON A.EMPID = e.EMPID

WHERE
B.EMP_ID = '1234'-

0 Likes