SQL pad results with extra rows

Question

I have a group of records in which there is a column "Store". I need to basically split the result sets into groups of 13 records, creating empty lines to lay out each store in order to have 13 lines. For simplicity, let's say I need groups of 4 entries.

An example below:

----------------- Store Name ----------------- A John A Bill B Sam C James C Tim C Chris D Simon D Phil 

I need the results to look like this:

 ----------------- Store Name ----------------- A John A Bill A B Sam B B C James C Tim C Chris D Simon D Phil D 

Is this possible with pure SQL? For each store there will never be more than three lines. SQL Fiddle

Answer 1

Try this option -

DDL:

 SET STATISTICS IO ON; IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp CREATE TABLE #temp ( Store CHAR(1) , Name VARCHAR(10) ) INSERT INTO #temp (Store, Name) VALUES ('A', 'John'), ('A', 'Bill'), ('B', 'Sam'), ('C', 'James'), ('C', 'Tim'), ('C', 'Chris'), ('D', 'Simon'), ('D', 'Phil') 

Inquiries

DevArt # 1:

 ;WITH cte AS ( SELECT Store , Name , rn = ROW_NUMBER() OVER (PARTITION BY Store ORDER BY (SELECT 1)) FROM #temp ) SELECT t.Store, Name = ISNULL(t3.Name, '') FROM ( SELECT DISTINCT Store FROM cte ) t CROSS JOIN (SELECT rn = 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2 LEFT JOIN cte t3 ON t2.rn = t3.rn AND t.Store = t3.Store 

DevArt # 2:

 SELECT t2.Store, Name = ISNULL(t3.Name, '') FROM ( SELECT * FROM ( SELECT Store, r = COUNT(1) FROM #temp GROUP BY Store ) t CROSS APPLY ( VALUES (r), (r+1), (r+2) ) t2 (x) ) t2 LEFT JOIN #temp t3 ON t2.Store = t3.Store AND t2.x = t2.r WHERE t2.x < 4 

Alexander Fedorenko:

 ;WITH cte AS ( SELECT DISTINCT Store FROM #temp ) SELECT o.Store, o.name FROM cte s CROSS APPLY ( SELECT TOP 3 x.Store, x.name FROM ( SELECT s2.Store, s2.name FROM #temp s2 WHERE s.Store = s2.Store UNION ALL SELECT s.Store, '' UNION ALL SELECT s.Store, '' ) x ) o 

ErikE:

 SELECT Store, Name FROM ( SELECT x.Store , x.Name , s = ROW_NUMBER() OVER (PARTITION BY x.Store ORDER BY xs) FROM #temp t CROSS APPLY ( VALUES (Store, Name, 0), (Store, '', 1), (Store, '', 1) ) x (Store, Name, S) ) z WHERE s <= 3 ORDER BY Store 

AmitSingh:

 SELECT t.Store, Name = COALESCE( ( SELECT name FROM ( SELECT row1 = ROW_NUMBER() OVER (PARTITION BY Store ORDER BY Store) , * FROM #temp ) c WHERE t.[row] = c.row1 AND t.Store = c.Store ) , '') FROM ( SELECT [Row] = ROW_NUMBER() OVER (PARTITION BY a.Store ORDER BY a.Store) , a.Store FROM ( SELECT Store FROM #temp GROUP BY Store ) a , ( SELECT TOP 3 Store FROM #temp ) b ) t 

Andriy M # 1:

 ;WITH ranked AS ( SELECT Store , Name , rnk = ROW_NUMBER() OVER (PARTITION BY Store ORDER BY 1/0) FROM #temp ) , pivoted AS ( SELECT Store , [1] = ISNULL([1], '') , [2] = ISNULL([2], '') , [3] = ISNULL([3], '') FROM ranked PIVOT ( MAX(Name) FOR rnk IN ([1], [2], [3]) ) p ) , unpivoted AS ( SELECT Store , Name FROM pivoted UNPIVOT ( Name FOR rnk IN ([1], [2], [3]) ) u ) SELECT * FROM unpivoted 

Andriy M # 2:

 ;WITH ranked AS ( SELECT Store , Name , rnk = ROW_NUMBER() OVER (PARTITION BY Store ORDER BY 1/0) FROM #temp ) , padded AS ( SELECT Store , Name FROM ranked PIVOT ( MAX(Name) FOR rnk IN ([1], [2], [3]) ) p CROSS APPLY ( VALUES (ISNULL([1], '')), (ISNULL([2], '')), (ISNULL([3], '')) ) x (Name) ) SELECT * FROM padded 

Output:

 Store Name ----- ---------- A John A Bill AB Sam BBC James C Tim C Chris D Simon D Phil D 

Statistics:

  Query Presenter Scans Logical Reads ------------------- ----- ------------- DevArt #1 3 41 DevArt #2 2 9 Alexander Fedorenko 4 5 ErikE 1 1 AmitSingh 22 25 Andriy M #1 1 1 Andriy M #2 1 1 

Request Cost:

Extended statistics:

Execution Statistics:

Plan request (from dbForge Studio for MS SQL):

Answer 2

 Select t.store_id,Coalesce((Select Name from ( Select row_Number() Over(Partition by store_id order by store_id) as row1, * from stores)c where t.row=c.row1 and t.store_id=c.store_id),'') as cfgg from (Select row_Number() Over(Partition by a.store_id order by a.store_id) as row, a.store_id from (Select store_id from stores group by store_id) a ,(Select top 3 store_id from stores)b ) t 

SQL Fiddle Demo

Answer 3

Another option with the APPLY operator

  ;WITH cte AS ( SELECT store_id FROM stores GROUP BY store_id ) SELECT o.store_id, o.name FROM cte s CROSS APPLY ( SELECT TOP 3 x.store_id, x.name FROM ( SELECT s2.store_id, s2.name FROM stores s2 WHERE s.store_id = s2.store_id UNION ALL SELECT s.store_id, '' UNION ALL SELECT s.store_id, '' ) x ) o 

SQLFiddle Demo

Answer 4

Here's a query that works (SQL Server 2008 and later can be fixed to work in 2005):

 SELECT Store, Name FROM ( SELECT X.Store, X.Name, R = Row_Number() OVER (PARTITION BY X.Store ORDER BY XS) FROM @temp T CROSS APPLY (VALUES (Store, Name, 0), (Store, '', 1), (Store, '', 1) ) X (Store, Name, S) ) Z WHERE R <= 3 ORDER BY Store ; 

According to SET STATISTICS IO ON; , here are the performance statistics (everyone has an insignificant processor with this small number of lines, maybe more lines will help determine the best performer):

  Query Presenter Scans Logical Reads ------------------- ----- ------------- ErikE 1 1 Alexander Fedorenko 4 5 Devart 3 41 AmitSingh 22 25 

My query does not preserve the “original” name order for each repository, however this is not a drawback because there is no concept of ordering in the relational database table. You must specify a column for the order if you want to keep a specific sequence.

Answer 5

Another option, this time using PIVOT and UNPIVOT :

 WITH ranked AS ( SELECT store_id, name, rnk = ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY 1/0) FROM stores ), pivoted AS ( SELECT store_id, [1] = ISNULL([1], ''), [2] = ISNULL([2], ''), [3] = ISNULL([3], '') FROM ranked PIVOT ( MAX(name) FOR rnk IN ([1], [2], [3]) ) p ), unpivoted AS ( SELECT store_id, name FROM pivoted UNPIVOT ( name FOR rnk IN ([1], [2], [3]) ) u ) SELECT * FROM unpivoted ; 

Demo version of SQL Fiddle for the game: http://sqlfiddle.com/#!3/354df/39 .

Note that the UNPIVOT step in the above query must be executed in a separate SELECT from the PIVOT action. This is because UNPIVOT does not create rows in which the columns listed in the IN column list contain NULL. However, you can replace UNPIVOT with an equivalent technique (e.g. CROSS APPLY) and thus move the expand to the same subquery that performs the rotation:

 WITH ranked AS ( SELECT store_id, name, rnk = ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY 1/0) FROM stores ), padded AS ( SELECT store_id, name FROM ranked PIVOT ( MAX(name) FOR rnk IN ([1], [2], [3]) ) p CROSS APPLY ( VALUES (ISNULL([1], '')), (ISNULL([2], '')), (ISNULL([3], '')) ) x (name) ) SELECT * FROM padded ; 

Demo of SQL Fiddle for the modified version: http://sqlfiddle.com/#!3/354df/40 .