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
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
Can you provide sample data and expected results? Did you try pivot?
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|
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
This is part of a sub query. the query itself is very big and tbl construct is big as well.
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.
It wont let me upload xlsx file it says has to be .png, jpg??
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'
HI
i tried this
hope it helps
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
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;
Thanks harishgg1!.. I'll try it out.
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 ;
Thanks mannesravya will try this seems easier .
Pasi
HI Harishgg your query gives me comma separated but I am looking for PIvot table results should be like this:
Thanks. Pasi.
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
Thanks Yosiasz.. I;ll try to make sense out of it.
hi pasi
i tried it
does this look okay ???
-- 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
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)
Thanks I tried it it looks ok.. need to add other part of query to it.
Pasi
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!
show us your main query. I would put it there.
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_LVLFROM 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'-