CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Misiu
Misiu

Reputation: 4919

Split returned rows into n groups and return them side by side as new columns - dynamic number of columns

I have a query that is returning 2 columns:

Product   | Price
------------------
Product01 | 10.00
Product02 | 10.00
Product03 | 10.00
Product04 | 10.00
Product05 | 10.00
Product06 | 10.00
Product07 | 10.00
Product08 | 10.00
Product09 | 10.00
Product10 | 10.00

What I'm trying to do is to return same data but in this form:

Product1   | Price1 | Product2   | Price2 | Product3   | Price3
---------------------------------------------------------------
Product01  | 10.00  | Product05  | 10.00  | Product08  | 10.00
Product02  | 10.00  | Product06  | 10.00  | Product09  | 10.00
Product03  | 10.00  | Product07  | 10.00  | Product10  | 10.00
Product04  | 10.00  | NULL       | NULL   | NULL       | NULL

This is my code so far:

DECLARE @COLUMNS INT
SET @COLUMNS = 3

;
WITH CTE AS (
SELECT
 ROW_NUMBER() OVER ( PARTITION BY [COLUMN] ORDER BY ROW ASC ) AS ID_IN_GROUP
,*
FROM (
    SELECT
        ROW_NUMBER() OVER ( ORDER BY Name ASC ) AS ROW
       ,NTILE(@COLUMNS) OVER ( ORDER BY Name ASC ) AS [COLUMN]
       ,*
    FROM
      Products
) X
)
SELECT A.Name as Product1, A.Price as Price1, B.Name as Product2, B.Price as Price2, C.Name as Product3, C.Price as Price3 FROM
(SELECT Name,Price,ID_IN_GROUP FROM CTE WHERE [COLUMN]=1) A
CROSS APPLY
(SELECT Name,Price, ID_IN_GROUP FROM CTE WHERE [COLUMN]=2) B
CROSS APPLY
(SELECT Name,Price, ID_IN_GROUP FROM CTE WHERE [COLUMN]=3) C
WHERE 
B.ID_IN_GROUP=A.ID_IN_GROUP
AND C.ID_IN_GROUP=A.ID_IN_GROUP

http://sqlfiddle.com/#!18/bf3b4/30

It is returning almost desired result:

Product1   | Price1 | Product2   | Price2 | Product3   | Price3
---------------------------------------------------------------
Product01  | 10.00  | Product05  | 10.00  | Product08  | 10.00
Product02  | 10.00  | Product06  | 10.00  | Product09  | 10.00
Product03  | 10.00  | Product07  | 10.00  | Product10  | 10.00

but the last line is missing - Product04. How can this be fixed?

EDIT: I've fixed that by changing CROSS APPLY to LEFT JOIN like this:

DECLARE @COLUMNS INT 
SET 
  @COLUMNS = 3;
WITH CTE AS (
  SELECT 
    ROW_NUMBER() OVER (
      PARTITION BY [COLUMN] 
      ORDER BY 
        ROW ASC
    ) AS ID_IN_GROUP, 
    * 
  FROM 
    (
      SELECT 
        ROW_NUMBER() OVER (
          ORDER BY 
            Name ASC
        ) AS ROW, 
        NTILE(@COLUMNS) OVER (
          ORDER BY 
            Name ASC
        ) AS [COLUMN], 
        * 
      FROM 
        Products
    ) X
) 
SELECT 
  A.Name as Product1, 
  A.Price as Price1, 
  B.Name as Product2, 
  B.Price as Price2, 
  C.Name as Product3, 
  C.Price as Price3 
FROM 
  (
    SELECT 
      Name, 
      Price, 
      ID_IN_GROUP 
    FROM 
      CTE 
    WHERE 
      [COLUMN] = 1
  ) A 
  LEFT JOIN (
    SELECT 
      Name, 
      Price, 
      ID_IN_GROUP 
    FROM 
      CTE 
    WHERE 
      [COLUMN] = 2
  ) B ON B.ID_IN_GROUP = A.ID_IN_GROUP 
  LEFT JOIN (
    SELECT 
      Name, 
      Price, 
      ID_IN_GROUP 
    FROM 
      CTE 
    WHERE 
      [COLUMN] = 3
  ) C ON C.ID_IN_GROUP = A.ID_IN_GROUP

http://sqlfiddle.com/#!18/bf3b4/61

Is this possible to do this more dynamic? So when I change @COLUMNS to 4 it will return 4 groups?

Upvotes: 2

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

What you are asking for is rather harder than I thought. I haven't quite figured out the logic, but this comes quite close:

SELECT MAX(CASE WHEN floor((seqnum - 1) / ceiling(cnt / 3.0)) = 0 THEN Name END) as Product1,
       MAX(CASE WHEN floor((seqnum - 1) / ceiling(cnt / 3.0)) = 0 THEN Price END) as Price1,
       MAX(CASE WHEN floor((seqnum - 1) / ceiling(cnt / 3.0)) = 1 THEN Name END) as Product2,
       MAX(CASE WHEN floor((seqnum - 1) / ceiling(cnt / 3.0)) = 1 THEN Price END) as Price2,
       MAX(CASE WHEN floor((seqnum - 1) / ceiling(cnt / 3.0)) = 2 THEN Name END) as Product3,
       MAX(CASE WHEN floor((seqnum - 1) / ceiling(cnt / 3.0)) = 2 THEN Price END) as Price3
FROM (SELECT p.*,
             ROW_NUMBER() OVER (ORDER BY Name) as seqnum,
             COUNT(*) OVER () as cnt
      FROM Products p
     ) p
GROUP BY (seqnum - 1) % ceiling(cnt / 3.0);

The difference is that this fills in the columns "greedily", so the columns are 4-4-2 rather than 4-3-3. One advantage of this approach is that if an 11th row is added, the columns remain the same.

The SQL Fiddle.

Upvotes: 1

Related Questions