SQL- pad results with extra rows

Question

I have a group of records that have a "Store" column. I need to basically split the result sets into groups of 13 records, creating blank rows to pad out each store to have 13 rows. For simplicity, lets say I need groups of 4 records.

Example, given the below table:

-----------------
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:

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

Is this at all possible with pure SQL? There are never going to be more than 3 rows for each store. SQL Fiddle

Answer 1

Try this one -

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')

Queries:

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 x.s)
   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
A     
B     Sam
B     
B     
C     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

Query cost:

Extended statistics:

Execution statistics:

Query plan (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

One more option with 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

Demo on SQLFiddle