Group by Column and Multiple Rows into One Row Multiple Columns

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;

Convert multiple rows into one row with multiple columns in T-SQL

Bracket [] your column names

Example or dbFiddle

SELECT *
FROM
(
  SELECT  CompanyName, Price, Project
  FROM dbo.MyTable
) src
PIVOT
(
  SUM(Price)
  FOR Project IN ([Cleaning], [Vaccuming], [Painting], [VendingMachines], [Wallpapers], [Carpeting])
) piv;

Combining rows into one row with multiple columns of data R

As @Duck mentioned you can do this

library(dplyr)
library(tidyr)

df3 %>% 
   group_by(ID) %>% 
   pivot_wider(names_from = TIME, 
               values_from = SH, 
               names_prefix = "SH_")

#> # A tibble: 3 x 7
#> # Groups:   ID [3]
#>      ID Sex    Race     PRE_WEIGHT POST_WEIGHT SH_POST   SH_PRE      
#>   <dbl> <chr>  <chr>         <dbl>       <dbl> <chr>     <chr>       
#> 1     1 FEMALE White           168         152 Poor      Good        
#> 2     2 MALE   Hispanic        157         143 Very Poor Poor        
#> 3     3 FEMALE Black           129         118 Good      Satisfactory

Your data

df3 <- readr::read_table("
ID  Sex         Race        TIME    SH           PRE_WEIGHT POST_WEIGHT
1   FEMALE      White       POST    Poor         168            152
1   FEMALE      White       PRE     Good         168            152
2   MALE        Hispanic    POST    Very Poor    157            143
2   MALE        Hispanic    PRE     Poor         157            143
3   FEMALE      Black       POST    Good         129            118
3   FEMALE      Black       PRE     Satisfactory 129            118")

Combine column from multiple rows into single row

If you have a known or maximum number of campaigns per orderId, consider a simple conditional aggregation in concert with row_number()

Example

Declare @YourTable Table ([orderId] int,[CampaignName] varchar(50),[DiscountAmount] varchar(50))  Insert Into @YourTable Values 
 (1,'50% Off','$5.20')
,(1,'10% Off','$1.30')
,(1,'5% Off','$0.57')

Select orderId
      ,Campaign1 = max(case when RN=1 then CampaignName end)
      ,Campaign2 = max(case when RN=2 then CampaignName end)
      ,Campaign3 = max(case when RN=3 then CampaignName end)
      ,Discount1 = max(case when RN=1 then DiscountAmount end)
      ,Discount2 = max(case when RN=2 then DiscountAmount end)
      ,Discount3 = max(case when RN=3 then DiscountAmount end)
 From  (
        Select *
              ,RN = row_number() over(partition by orderId order by (select null))
         From @YourTable
       ) A
 Group By OrderID

Returns

orderId Campaign1   Campaign2   Campaign3   Discount1   Discount2   Discount3
1       50% Off     10% Off     5% Off      $5.20       $1.30       $0.57

Excel - condense multiple rows into a single row of multiple columns

You can first prepare your table how you want.

then you can use this formula to get the
=TRANSPOSE(FILTER(Data!$F$4:$F$38,(Data!$A$4:$A$38=$A4)*(Data!$C$4:$C$38=B$2),""))

Pivot Numbered List to Multiple Rows of Set Number of Columns

unpivot with values() and calculate the group of 4 and the column name ordinal of 4 by index.

CREATE TABLE #tmpTest (
    CreatedAt Date,
    [Index] INT,
    [Name] VARCHAR(32), Other VARCHAR(20), Another VARCHAR(20)
);

INSERT INTO #tmpTest ([CreatedAt], [Index], [Name], Other, Another)
VALUES('2022-07-20',    1,  'David', 'a', 'b'),
('2022-07-20',  2,  'Haley', 'c', 'd'),
('2022-07-20',  3,  'John', 'e', 'f'),
('2022-07-20',  4,  'Mark', 'g', 'h'),
('2022-07-20',  5,  'Matt', 'i', 'j'),
('2022-07-20',  6,  'Sarah', 'k', 'l'),
('2022-08-13',  1,  'David', 'm', 'n'),
('2022-08-13',  2,  'Haley', 'o', 'p'),
('2022-08-13',  3,  'John', 'q', 'r');


SELECT P.*
FROM (SELECT T.[CreatedAt],U.COL,U.GRP,U.VAL
FROM #tmpTest AS T
CROSS APPLY(VALUES
(CONCAT('Name',1+(T.[index]-1)%4),1+(T.[index]-1)/4,T.Name), 
(CONCAT('Other',1+(T.[index]-1)%4),1+(T.[index]-1)/4,T.Other), 
(CONCAT('Another',1+(T.[index]-1)%4),1+(T.[index]-1)/4,T.Another)
) AS U(COL,GRP,VAL)) AS D
PIVOT(MAX(VAL) FOR COL IN (Name1,Other1,Another1,Name2,Other2,Another2,Name3,Other3,Another3,Name4,Other4,Another4)) AS P;

---

Related Topics