T-SQL Group Rows into Columns

Rows Into Columns and Grouping

This looks like a job for pivot if you're using SQL Server 2005 or later.

EDIT:

Since you already know about pivot, you know it does almost what you need.

You already have the following query:

SELECT
    MAX(OrganizationName) as OrganizationName,
    OrganizationID,
    ReceivableStatus,
    SUM(InvoiceFee) as TotalDue
FROM v_InvoicesFreelanceOutstanding
GROUP BY OrganizationID, ReceivableStatus

Which gives you the current, 30-60, 60-90 and 90+ parts that you need. If you pivot that, you get everything you need except for your total. So just throw in the total:

(SELECT
    MAX(OrganizationName) as OrganizationName,
    OrganizationID,
    ReceivableStatus,
    SUM(InvoiceFee) as TotalDue
FROM v_InvoicesFreelanceOutstanding
GROUP BY OrganizationID, ReceivableStatus)
UNION
(SELECT
    MAX(OrganizationName) as OrganizationName,
    OrganizationID,
    'Total' AS ReceivableStatus,
    SUM(InvoiceFee) as TotalDue
FROM v_InvoicesFreelanceOutstanding
GROUP BY OrganizationID)

Pivot on this result and you should get the output you want:

SELECT *
FROM 
    [the query above]
PIVOT (
    SUM(TotalDue)
    FOR ReceivableStatus IN ([Current],[30-60 days],[60-90 days],[over 90 days],[Total]) 
)

T-SQL Group Rows Into Columns

You might pivot the table using row_number() as a source of column names:

select *
from
(
  select ref, 
         name, 
         link,
         row_number() over (partition by ref, name order by link) rn
  from table1
) s
pivot (min (link) for rn in ([1], [2], [3], [4])) pvt

Simply extend the list of numbers if you have more rows.

Live test is @ Sql Fiddle.

Group by column and multiple Rows into One Row multiple columns

As I mention in the comments, what you need here is a PIVOT or Cross tab; I prefer the latter so what I am going to use.

The non-dynamic solution to this would be as follows:

WITH RNs AS(
    SELECT WorkOrder,
           TestType,
           Result,
           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column
    FROM dbo.Result)
SELECT WorkOrder,
       TestType,
       MAX(CASE RN WHEN 1 THEN Result END) AS Result1,
       MAX(CASE RN WHEN 2 THEN Result END) AS Result2,
       MAX(CASE RN WHEN 3 THEN Result END) AS Result3
FROM RNs R
GROUP BY WorkOrder,
         TestType;

The problem, however, is that this "locks" you into 3 results, but you suggest there is an indeterminate number of results. Therefore you need a dynamic solution.

The below will work up to 100 results. if you do need more columns than than, then add more CROSS JOINs to N in the CTE Tally. This results is something like this (which is quite messy).

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10),
        @MaxTally int;

SELECT @MaxTally = MAX(C)
FROM (SELECT COUNT(*) AS C
      FROM dbo.Result
      GROUP BY WorkOrder,
               TestType) R;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2) --100 rows, add more Ns for more rows
SELECT @SQL = N'WITH RNs AS(' + @CRLF +
              N'    SELECT WorkOrder,' + @CRLF +
              N'           TestType,' + @CRLF +
              N'           Result,' + @CRLF +
              N'           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column' + @CRLF +
              N'    FROM dbo.Result)' + @CRLF +
              N'SELECT WorkOrder,' + @CRLF +
              N'       TestType,' + @CRLF +
              --Using FOR XML PATH due to not knowing SQL Server version
              STUFF((SELECT N',' + @CRLF +
                            CONCAT(N'       MAX(CASE RN WHEN ',T.I,N' THEN Result END) AS Result',T.I)
                     FROM Tally T
                     ORDER BY T.I ASC
                     FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + @CRLF +
              N'FROM RNs R' + @CRLF +
              N'GROUP BY WorkOrder,' + @CRLF +
              N'         TestType;';

PRINT @SQL; --Your best friend.

EXEC sys.sp_executesql @SQL;

SQL Pivot group by rows to columns

This will take a few steps

• We use CAST AS date to get the date without the time
• ROW_NUMBER and LEAD help us identify the beginning and end of each grouping
• Then simply aggregate by Name and the date
• And use conditional aggregation to get the results we want
• To get a time value, we add number of seconds to 0:00:00 time

SELECT
  Name,
  DayPerformed,
  StartDateAndTime = MIN(StartDateAndTime),
  EndDateAndTime = MAX(EndDateAndTime),
  StartText = MIN(CASE WHEN rn = 1 THEN StartText END),
  EndText = MIN(CASE WHEN nxt IS NULL THEN EndText END),
  Loading = DATEADD(ms,
      ISNULL(SUM(CASE WHEN WorkState = 'Loading' THEN DATEDIFF(ms, StartDateAndTime, EndDateAndTime) END), 0),
      CAST('0:00:00' AS time)),
  Driving = DATEADD(ms, 
      ISNULL(SUM(CASE WHEN WorkState = 'Driving' THEN DATEDIFF(ms, StartDateAndTime, EndDateAndTime) END), 0),
      CAST('0:00:00' AS time)),
  Pause   = DATEADD(ms,
      ISNULL(SUM(CASE WHEN WorkState = 'Pause'   THEN DATEDIFF(ms, StartDateAndTime, EndDateAndTime) END), 0), 
      CAST('0:00:00' AS time))
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY Name, DayPerformed ORDER BY StartDateAndTime),
      nxt = LEAD(WorkState) OVER (PARTITION BY Name, DayPerformed ORDER BY StartDateAndTime)
    FROM tblWorkingTimes wt
    CROSS APPLY (VALUES (CAST(StartDateAndTime AS date))) v(DayPerformed)
) wt
GROUP BY
  Name,
  DayPerformed;

To aggregate by month simply replace DayPerformed with

CROSS APPLY (VALUES (EOMONTH(StartDateAndTime))) v(DayPerformed)

db<>fiddle

Convert rows into columns by grouping one row value

Try this:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' +  
                     QUOTENAME('Director' + CAST(ROW_NUMBER() 
                                        OVER(PARTITION BY c.companyname 
                                             ORDER BY c.companyname) 
                                      AS VARCHAR(10)))
               FROM CompaniesDirectors c
               FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')  ,1,1,'');

SET @query = 'SELECT companyname,  ' + @cols + ' from 
             (
                SELECT 
                  c.CompanyName, 
                  c.DirectorName,  
                  ''Director'' + CAST(ROW_NUMBER() 
                                      OVER(PARTITION BY c.companyname 
                                      ORDER BY c.companyname) 
                                    AS VARCHAR(10)) director_num
                FROM CompaniesDirectors c
             ) x
             PIVOT 
             (
                MAX(directorname)
                FOR director_num IN (' + @cols + ')
             ) p ';

EXECUTE(@query);

This should give you something like:

companyname    Director1    Director2    Director3    Director4
    AB            Foo          Bar           rr          tt
    ADS           Rai          Rao           Raj         Rio

SQL Fiddle Demo¹

^{1: Thanks to @bluefeet}

How can I group rows into columns with multiple values?

declare @t table (group1 varchar(10),group2 varchar(20),group3 int,val1 int,val2 int,val3 int)

insert into @t (group1,group2,group3,val1,val2,val3)values ('John','Smith',25,1,0,0),
('John','Smith',25,1,6,0),
('John','Smith',25,1,0,8),
('Chris','Green',30,1,0,0),
('Chris','Green',30,1,3,0),
('Chris','Green',30,5,0,0)

select distinct group1,group2,group3,MAX(val1),MAX(val2),MAX(val3) from @t
group by group1,group2,group3 
ORDER BY group1 desc,group2 desc

Efficiently convert rows to columns in sql server

There are several ways that you can transform data from multiple rows into columns.

Using PIVOT

In SQL Server you can use the PIVOT function to transform the data from rows to columns:

select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
  select value, columnname
  from yourtable
) d
pivot
(
  max(value)
  for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;

See Demo.

Pivot with unknown number of columnnames

If you have an unknown number of columnnames that you want to transpose, then you can use dynamic SQL:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

See Demo.

Using an aggregate function

If you do not want to use the PIVOT function, then you can use an aggregate function with a CASE expression:

select
  max(case when columnname = 'FirstName' then value end) Firstname,
  max(case when columnname = 'Amount' then value end) Amount,
  max(case when columnname = 'PostalCode' then value end) PostalCode,
  max(case when columnname = 'LastName' then value end) LastName,
  max(case when columnname = 'AccountNumber' then value end) AccountNumber
from yourtable

See Demo.

Using multiple joins

This could also be completed using multiple joins, but you will need some column to associate each of the rows which you do not have in your sample data. But the basic syntax would be:

select fn.value as FirstName,
  a.value as Amount,
  pc.value as PostalCode,
  ln.value as LastName,
  an.value as AccountNumber
from yourtable fn
left join yourtable a
  on fn.somecol = a.somecol
  and a.columnname = 'Amount'
left join yourtable pc
  on fn.somecol = pc.somecol
  and pc.columnname = 'PostalCode'
left join yourtable ln
  on fn.somecol = ln.somecol
  and ln.columnname = 'LastName'
left join yourtable an
  on fn.somecol = an.somecol
  and an.columnname = 'AccountNumber'
where fn.columnname = 'Firstname'

Transpose group of rows into multiple columns

We can try doing a pivot query with the help of ROW_NUMBER:

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ContactID ORDER BY CatCode, CatDesc) rn
    FROM CntTyp
)

SELECT
    ContactID,
    MAX(CASE WHEN rn = 1 THEN CatCode END) AS CatCode1,
    MAX(CASE WHEN rn = 1 THEN CatDesc END) AS CatDesc1,
    MAX(CASE WHEN rn = 2 THEN CatCode END) AS CatCode2,
    MAX(CASE WHEN rn = 2 THEN CatDesc END) AS CatDesc2,
    MAX(CASE WHEN rn = 3 THEN CatCode END) AS CatCode3,
    MAX(CASE WHEN rn = 3 THEN CatDesc END) AS CatDesc3
FROM cte
GROUP BY
    ContactID;

---

Related Topics