SQL Server : pivot a single column with fixed number or columns

Question

I have this simple query that brings the siblings of a given item.

select
    PC.SKU
from
    ProdC PC
where
    Parent_ID in (select Parent_ID
                  from ProdC
                  where SKU = 4536)
    and ParentFlag <> 'P'
    and SKU <> 4536

I'd like to display up to 6 siblings horizontally. So it would look something like this:

Sib1    Sib2    Sib3    Sib4    Sib5    Sib6
=============================================
4532    4539    4548    4552    4561    4562
3512    3536    
5632    5636    5640

Now each of these row are for a different item. some parent have 2 child, some have up to 8 but I only want to show 6 max. There's a priority column for the children. I can sort it by that column desc to get the 6 newest child.

Any help appreciated.

This sounds like a crosstab, also known as conditional aggregation. If you can post some sample data and table structure this is pretty simple. Without those details it is very challenging to help. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, May 19 '16 at 19:42
Possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Tab Alleman, May 19 '16 at 20:08
Based on the language of your question, you seem to already know this is a PIVOT query, so why haven't you simply used the PIVOT command in SQL? If you have made any effort to research this yourself, please add it to your question. — Tab Alleman, May 19 '16 at 20:09

M.Ali · Accepted Answer · 2016-05-19T20:54:44.177

I have a table in my test database with records with multiple child records for each parent record, created the following query to only get the top 6 child records, but it only picks the immediate child records, if you have another level of children records then you probably need to look into recursive CTE etc.

;WITH X AS 
   (
    Select * 
         ,ROW_NUMBER() OVER (PARTITION BY Parent_ID ORDER BY ID) rn
    from TableName 
),
Y AS (
      Select * , 'Sib' + Cast(rn AS Varchar(10)) Sibs
      FROM X
      Where rn < 7
)
 Select *
 from 
    (
    Select Parent_ID , Sibs , ID
    FROM Y
    ) a
     PIVOT (MAX(ID)
            FOR Sibs
            IN (Sib1,Sib2,Sib3,Sib4,Sib5,Sib6))p

Result Set

+----------+------+------+------+------+-------+------+
| ParentID | Sib1 | Sib2 | Sib3 | Sib4 | Sib5  | Sib6 |
+----------+------+------+------+------+-------+------+
|        0 |    0 |  139 | 258  | 266  | 285   | 500  |
|      139 |  140 |  141 | 142  | 143  | 144   | 162  |
|      142 | 5062 | 5063 | NULL | NULL | NULL  | NULL |
|      143 | 5041 | 5042 | 5043 | 5044 | 5045  | 5046 |
|      144 | 5050 | 5051 | 5052 | 5053 | 5054  | 5055 |
|      258 | 5823 | 5824 | 5825 | 5826 | 11269 | NULL |
|      266 | 5822 | 5912 | 5913 | 5914 | 5915  | 5916 |
|      285 | 2139 | 3855 | 4172 | 4173 | NULL  | NULL |
+----------+------+------+------+------+-------+------+

EDIT

After you have provided some sample data your query should look something like..

;WITH X AS 
   (
    Select * 
         ,ROW_NUMBER() OVER (PARTITION BY Parent_Sku ORDER BY sku) rn
    from #ProdC 
),
Y AS (
      Select * , 'Sib' + ISNULL(NULLIF(Cast(rn -1 AS Varchar(10)), '0'),'') Sibs
      FROM X
      Where rn < 8
)
 Select Sib1,Sib2,Sib3,Sib4,Sib5,Sib6
 from 
    (
    Select Parent_Sku , Sibs , sku
    FROM Y
    ) a
     PIVOT (MAX(sku)
            FOR Sibs
            IN (Sib,Sib1,Sib2,Sib3,Sib4,Sib5,Sib6,Sib7))p

Result set

| Sib1 | Sib2 | Sib3 |   Sib4 |   Sib5 |   Sib6 |
|------|------|------|--------|--------|--------|
| 4532 | 4536 | 4539 |   4548 |   4552 | (null) |
| 3512 | 3536 | 4561 |   4562 | (null) | (null) |
| 5632 | 5636 | 5640 | (null) | (null) | (null) |

I don't want to bring the parent record, Just want to list up the children for the same parent, up to 6 in each row. Here's my sample table sqlfiddle.com/#!3/6a096 — Eclipse, May 19 '16 at 20:22
M. Ali, Thanks so much for your help with this. So if I were to use this in a stored procedure and want to return all siblings of a given child SKU, how do you think I should supply it in a variable and return all siblings of a given SKU? for an example, this query would take 4536 as an input and supply 4532, 4539, 4548, 4552, 4561, 4562 as a result set. I've updated the sqlfiddle table with the right source table and data. — Eclipse, May 23 '16 at 17:19
You need to accept this answer for the problem you presented here, and ask another question separately. — M.Ali, May 23 '16 at 19:02

SQL Server : pivot a single column with fixed number or columns

1 Answers1

Result Set