Transform rows into columns in sql server

Question

I am having tough time to figure out this please help me I have time in/out sql query I have a table looks like below. there are four columns display time in/out info such as...

I am trying to convert rows into column and display such information in below

I have tried to use it but select first column only

select [tblDataYears_Year],[tblDataYears_Grade],[tblDataYears_ServicePeriod],[tblDataYears_DirectAnnual] from [tblDataYears]
declare @s varchar(8000)
select  @s = COALESCE(@s + ',', '') + cast([tblDataYears_Year] as varchar(10)) + ' as number'
from    [tblDataYears]
exec('select '+@s)

SQL Commands for solution:

CREATE TABLE [dbo].[tblDataYears](
    [tblDataYears_Id] [int] IDENTITY(1,1) NOT NULL,
    [tblDataYears_Year] [nvarchar](50) NULL,
    [tblDataYears_Grade] [int] NULL,
    [tblDataYears_ServicePeriod] [int] NULL,
    [tblDataYears_DirectAnnual] [date] NULL,
    [tblDataYears_IsDeleted] [bit] NULL,
    [FKEmp_Id] [int] NULL)
INSERT [dbo].[tblDataYears] ([tblDataYears_Id], [tblDataYears_Year], [tblDataYears_Grade], [tblDataYears_ServicePeriod], [tblDataYears_DirectAnnual], [tblDataYears_IsDeleted], [FKEmp_Id]) VALUES (1, N'1435', 10, 16, CAST(N'2018-01-08' AS Date), 0, 1)
INSERT [dbo].[tblDataYears] ([tblDataYears_Id], [tblDataYears_Year], [tblDataYears_Grade], [tblDataYears_ServicePeriod], [tblDataYears_DirectAnnual], [tblDataYears_IsDeleted], [FKEmp_Id]) VALUES (2, N'1436', 11, 17, CAST(N'2018-01-08' AS Date), 0, 1)
INSERT [dbo].[tblDataYears] ([tblDataYears_Id], [tblDataYears_Year], [tblDataYears_Grade], [tblDataYears_ServicePeriod], [tblDataYears_DirectAnnual], [tblDataYears_IsDeleted], [FKEmp_Id]) VALUES (3, N'1437', 12, 18, CAST(N'2018-01-08' AS Date), 0, 1)
INSERT [dbo].[tblDataYears] ([tblDataYears_Id], [tblDataYears_Year], [tblDataYears_Grade], [tblDataYears_ServicePeriod], [tblDataYears_DirectAnnual], [tblDataYears_IsDeleted], [FKEmp_Id]) VALUES (4, N'1438', 13, 19, CAST(N'2018-01-08' AS Date), 0, 1)
INSERT [dbo].[tblDataYears] ([tblDataYears_Id], [tblDataYears_Year], [tblDataYears_Grade], [tblDataYears_ServicePeriod], [tblDataYears_DirectAnnual], [tblDataYears_IsDeleted], [FKEmp_Id]) VALUES (5, N'1439', 14, 20, CAST(N'2018-01-08' AS Date), 0, 1)
INSERT [dbo].[tblDataYears] ([tblDataYears_Id], [tblDataYears_Year], [tblDataYears_Grade], [tblDataYears_ServicePeriod], [tblDataYears_DirectAnnual], [tblDataYears_IsDeleted], [FKEmp_Id]) VALUES (6, N'1440', 15, 21, CAST(N'2018-01-08' AS Date), 0, 1)
INSERT [dbo].[tblDataYears] ([tblDataYears_Id], [tblDataYears_Year], [tblDataYears_Grade], [tblDataYears_ServicePeriod], [tblDataYears_DirectAnnual], [tblDataYears_IsDeleted], [FKEmp_Id]) VALUES (7, N'1441', 16, 22, CAST(N'2018-01-08' AS Date), 0, 1)
INSERT [dbo].[tblDataYears] ([tblDataYears_Id], [tblDataYears_Year], [tblDataYears_Grade], [tblDataYears_ServicePeriod], [tblDataYears_DirectAnnual], [tblDataYears_IsDeleted], [FKEmp_Id]) VALUES (8, N'1442', 17, 23, CAST(N'2018-01-08' AS Date), 0, 1)

check given link to convert [Row To Column](https://www.databasejournal.com/features/mssql/converting-rows-to-columns-pivot-and-columns-to-rows-unpivot-in-sql-server.html). — Hiren Patel, Aug 23 '18 at 09:12
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — JohnHC, Aug 23 '18 at 09:12
pivot don't work correctly because determine number of columns but i want to use my code i tried it but i want to work for more than one column. — argtsm, Aug 23 '18 at 09:28
This type of operation is often asked here, so you should be able to find it. But if you need our help, please provide sample data and desired output. (in a create table, insert data and so on, a sql statement, not pictures). Then we can probably help you out. — SQL_M, Aug 23 '18 at 09:38

Gordon Linoff · Accepted Answer · 2018-08-24T01:42:51.120

This is complicated because it requires both an unpivot and a pivot. I would approach this as:

select v.which,
       max(case when seqnum = 1 then val end),
       max(case when seqnum = 2 then val end),
       max(case when seqnum = 3 then val end),
       max(case when seqnum = 4 then val end)
from (select t.*,
             row_number() over (order by tblDataYears_Id) as seqnum
      from tblDataYears t
     ) t cross apply
     (values (t.tblDataYears_Year, 'Year'),
             (t.tblDataYears_Grade, 'Grade'),
             (t.tblDataYears_ServicePeriod, 'ServicePeriod'),
             (t.tblDataYears_DirectAnnual, 'DirectAnnual)
     ) v(val, which)
group by v.which;

If you need a variable number of columns, then you can transform this into dynamic SQL -- the only change is to the SELECT clause.

Msg 156, Level 15, State 1, Line 53 Incorrect syntax near the keyword 'cross'. — argtsm, Aug 23 '18 at 17:55

Transform rows into columns in sql server

1 Answers1