Rows into Columns In SQL Server (May be Pivot)

Question

I need to transform Rows data into columns on a specif condition. Assume that we are in year of 2015 then It needs to fetch next years data as a new column. Please have a look at the Table A and can some one suggest how can I achieve expected result?

Table A:

---

EmpID   Year     Price     Cm       St1    St2 
03      2015      1        AB       1          0
03      2016      2        CD       0          1
04      2016      2        XY       0          1
04      2015      20       ZX       0          1 

Expected Output:

---

EmpID  Year Y1_Price Y1_Cm  Y1_St1  Y1_St2 Y2_Price Y2_Cm  Y2_St1  Y2_St2
03     2015  1       AB     1       0      2        CD     0       1
03     2016  2       CD     0       1      NULL     NULL   NULL    NULL 
04     2016  2       XY     0       1      NULL     NULL   NULL    NULL
04     2015  20      ZX     0       1      2        XY     0       1

Please help me out to write the SQL for the below scenario

Answer 1

Please over complicate the thing. I have spent around 10 mins to write this query and it is very easy to achieve this.

@Sandeep - Use below script and it will give you exactly what you are looking for. Assuming Test is your table name.

Query:

WITH CTE AS
(
SELECT EmpId, Year, Price, Cm, St1, St2, ROW_NUMBER() OVER(PARTITION BY EmpId ORDER BY Year) RNo
FROM TEST
)
SELECT T1.EmpId, T1.Year
    , T1.Price Y1_Price, T1.Cm Y1_Cm, T1.St1 Y1_St1, T1.St2 Y1_St2
    , T2.Price Y2_Price, T2.Cm Y2_Cm, T2.St1 Y2_St1, T2.St2 Y2_St2
FROM CTE T1
LEFT JOIN CTE T2 ON T1.EmpId = T2.EmpId AND T1.RNo + 1 = T2.RNo

Answer 2

Assuming you are doing a Year over Year Comparison

Declare @Table table (EmpID varchar(25),Year int,Price money,CM varchar(25), St1 int,St2 int)
Insert into @Table (EmpID,Year,Price,Cm,St1,St2) values
('03',2015,1,'AB',1,0),
('03',2016,2,'CD', 0,1),
('04',2016,2,'XY',0,1),
('04',2015,20,'ZX',0,1)

Select A.EmpID
      ,A.Year
      ,Y1_Price = A.Price
      ,Y1_CM    = A.CM
      ,Y1_St1   = A.ST1
      ,Y1_St2   = A.ST2
      ,Y2_Price = B.Price
      ,Y2_CM    = B.CM
      ,Y2_St1   = B.ST1
      ,Y2_St2   = B.ST2
 From @Table A
 Left Join @Table B on (A.EmpID = B.EmpID and A.Year+1=B.Year)

Returns

EmpID   Year    Y1_Price    Y1_CM   Y1_St1  Y1_St2  Y2_Price    Y2_CM   Y2_St1  Y2_St2
03      2015    1.00        AB      1       0       2.00        CD      0       1
03      2016    2.00        CD      0       1       NULL        NULL    NULL    NULL
04      2016    2.00        XY      0       1       NULL        NULL    NULL    NULL
04      2015    20.00       ZX      0       1       2.00        XY      0       1

Answer 3

Okay, below is an example of a query I wrote which does something similar to what you want. It takes a set of data and dynamically pivots based on the starting year. I've only given you the first pivot (for Price). When you want to pivot on the second column and beyond, you'll need to create a second dynamic select string, and then join on the new pivot in the dynamic SQL further down.

declare @startYear int = 2010;
declare @startDate datetime = '1/1/2010 00:00:00.000';
declare @endYear int = year(getutcdate());
declare @endDate datetime = convert(datetime, '12/31/' + convert(nvarchar, @endYear) + ' 23:59:59.997');

-- our variables for string concatenation
declare     @yearString             nvarchar(max)   = ''
            , @piv1YearSelectionString  nvarchar(max)   = ''
            , @query                    nvarchar(max)   = ''
;

-- check for the existence of our temp table and dropping it
if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U') 
    and o.id = object_id(N'tempdb..#rawData')
)
begin
    drop table #rawData;
end

-- create our temp table
create table  #rawData
(
    EmployeeId          nvarchar(50)
    , InvoiceYear       int
    , Price             money
    , Cm                nvarchar(50)
    , St1               int
    , St2               int
);

-- concatenate our strings with our year column names
while (@startYear <= @endYear)
begin
    -- creates a string of values like [2010, 2011, ...]
    set @yearString                     = @yearString + '[' + convert(nvarchar, @startYear) + ']';
    -- creates a string of values like ['COALESCE(MAX[a].[2011], 0) as [Price2011], ...
    set @piv1YearSelectionString        = @piv1YearSelectionString + 'COALESCE(MAX([a].[' + convert(nvarchar, @startYear) + ']), 0) AS [Price' + convert(nvarchar, @startYear) + ']';

    set @startYear                      = @startYear + 1;

    -- adds commas to the strings
    if @startYear <= @endYear
    begin
        set @yearString                 = @yearString + ', ';
        set @piv1YearSelectionString    = @piv1YearSelectionString + ', ';
    end
end
;

-- build the query here
set @query = '
declare @sDate datetime = ''' + convert(varchar, @startDate) + ''';
declare @eDate datetime = dateadd(s, -1, dateadd(yyyy, 1, @sDate));
declare @stopDate datetime = ''' + convert(varchar, @endDate) + ''';

while @sDate < @stopDate
begin

    -- uncomment the line below to see the dates
    --select @sDate, @eDate;

    -- collect your input table
    insert #rawData ( employeeId, invoiceYear, Price, Cm, St1, St2 )
    select
        EmployeID
        , year(InvoiceDate) as InvoiceYear
        , Price
        , Cm
        , St1
        , St2

    from
        <SourceQuery>
    where
        InvoiceDate between @sdate and @eDate
    ;

    set @sDate = dateadd(yyyy, 1, @sDate);
    set @eDate = dateadd(ms, -1, dateadd(yyyy, 1, @sDate));
end
 -- dynamically pivot
select 
    a.EmployeeId
    , ' + @piv1YearSelectionString + '
from
(
    select
        piv1.CompanyId, piv1.CompanyName, ' + @yearString + '
    from
        #rawData
    pivot
    (
        max(Price)
        for [invoiceYear] in (' + @yearString + ')
    ) as piv1
) a

group by
    a.EmployeeId

order by
    a.EmployeeId
';

-- run the query
execute(@query);

-- clean up
drop table #rawdata;