How to get result in the row instead of column

Transact-SQL
1

How to get result in the row instead of column. Thank you
This is my sql query and output

select a.EMPLOYEE, a.LAST_NAME, a.FIRST_NAME, DATEDIFF(YEAR,b.BIRTHDATE, c.START_DATE) as Age,
b.BIRTHDATE, c.START_DATE, c.PLAN_CODE, c.SMOKER, c.EMP_AFT_CONT, c.COV_OPTION
from EMPLOYEE a
right outer join PAEMPLOYEE b on a. EMPLOYEE = b.EMPLOYEE
right outer join (Select distinct EMPLOYEE, START_DATE, PLAN_CODE, SMOKER, EMP_AFT_CONT, COV_OPTION from BENEFIT
where PLAN_CODE in ('GAP1', 'CI01', 'CI21'))c
on a.EMPLOYEE = c.EMPLOYEE
order by EMPLOYEE, START_DATE

EmpID Name Age Start_Date Plan_Code Smoker EmpRate CoverOption
1049 John Doe 59 2014-01-01 GAP1 Y 623.00 3
1049 John Doe 59 2014-01-01 CI21 N 422.08 3
1049 John Doe 60 2015-01-01 CI21 N 288.20 2
1049 John Doe 60 2015-01-01 CI01 Y 488.402 3
1049 John Doe 60 2015-01-01 GAP1 N 250.20 22**************************************************************
I WANT OUTPUT LIKE THIS
EmpID Name Age StartDate Plancode Smoker EmpRate CoverOpt
1049 John Doe 59 2014 GAP1 Y 623.00 3
CI21 N 422.08 3
1049 John Doe 60 2015 CI21 N 288.20 2
CI01 Y 488.40 3
GAP1 N 250.20 3
AND SO ON...

2

Is this for SQL Server (or mysql or something else)?

3

Could you please provide create table and insert statement scripts.

4

hi

i have created the script

drop create data .. 
DROP TABLE [dbo].[data];
GO



CREATE TABLE [data]
(
    [EMPID] INT NULL,
    [NAME] VARCHAR(100) NULL,
    [AGE] INT NULL,
    [START_DATE] DATE NULL,
    [PLAN_CODE] VARCHAR(100) NULL,
    [SMOKER] VARCHAR(100) NULL,
    [EmpRate] DECIMAL(10, 2) NULL,
    [COVEROPTION] INT NULL,
);
GO

INSERT INTO [dbo].[data]
SELECT 1049,
       'John Doe',
       59,
       '2014-01-01',
       'GAP1',
       'Y',
       623.00,
       3;
INSERT INTO [dbo].[data]
SELECT 1049,
       'John Doe',
       59,
       '2014-01-01',
       'CI21',
       'N',
       422.08,
       3;
INSERT INTO [dbo].[data]
SELECT 1049,
       'John Doe',
       60,
       '2015-01-01',
       'CI21',
       'N',
       288.20,
       2;
INSERT INTO [dbo].[data]
SELECT 1049,
       'John Doe',
       60,
       '2015-01-01',
       'CI01',
       'Y',
       488.402,
       3;
INSERT INTO [dbo].[data]
SELECT 1049,
       'John Doe',
       60,
       '2015-01-01',
       'GAP1',
       'N',
       250.20,
       4;
GO



SELECT *
FROM [dbo].[data];
GO
5

here is the SQL
comma seperated string ... output

SQL ... 
;WITH cte
AS (SELECT EMPID,
           NAME,
           AGE,
           START_DATE,
           PLAN_CODE,
           SMOKER,
           EmpRate,
           COVEROPTION
    FROM [dbo].[data])
SELECT SS.EMPID,
       SS.NAME,
       SS.AGE,
       SS.START_DATE,
       STUFF(
       (
           SELECT '; ' + PLAN_CODE + ' ' + SMOKER + ' ' + CAST(EmpRate AS VARCHAR) + ' ' + CAST(COVEROPTION AS VARCHAR)
           FROM cte US
           WHERE US.EMPID = SS.EMPID
                 AND US.NAME = SS.NAME
                 AND US.AGE = SS.AGE
                 AND US.START_DATE = SS.START_DATE
           FOR XML PATH('')
       ),
       1,
       1,
       ''
            ) [SECTORS/USERS]
FROM cte SS
GROUP BY SS.EMPID,
         SS.NAME,
         SS.AGE,
         SS.START_DATE
ORDER BY 1;
Result

image
image.png832×154 8.68 KB

6

yes this is MySQL.

7

Ouch. I can't help with MySQL for something like that, only with SQL Server.

8

thank you very much! it works perfect.