SQL query to dynamic Pivot/Transform data from table

Question

I have the below table,

Company_ID             part_id     Serial NO
------------------    ----------- --------------
1                     BAU          BAU2512  
1                     BAU          BAU3512
1                     BAU          BAU4512
2                     BAU          BAU5512
2                     BAU          BAU6512
2                     BAU          BAU7512

And I want a query to return this

Company_id             Item#1       Item#2         Item#3   
------------------    ---------     -------------  ----------- 
1                     BAU2512       BAU3512        BAU4512
2                     BAU5512       BAU6512        BAU7512

any help with the query appreciated, using SQL Server 2008

To create the sample table.

-- Suppress data loading messages
SET NOCOUNT ON

-- Create Sample Data using a Table Varable
DECLARE @Company TABLE
(Company_ID int,
 part_ID varchar(30),
 SerialNO varchar(30))

-- Load Sample Data
INSERT INTO @Company VALUES (1, 'BAU', 'BAU2512')
INSERT INTO @Company VALUES (1, 'BAU', 'BAU3512')
INSERT INTO @Company VALUES (1, 'BAU', 'BAU4512')
INSERT INTO @Company VALUES (2, 'BAU', 'BAU5512')
INSERT INTO @Company VALUES (2, 'BAU', 'BAU6512')
INSERT INTO @Company VALUES (2, 'BAU', 'BAU7512')

tried it via a subquery below but sql server throwing more than one row returned error, still battling. SELECT c.Company_ID, (select cc.serialNo from Company cc where cc.Company_ID = c.Company_ID) from Company c — Ricky Gummadi, May 14 '12 at 03:49

score 2 · Accepted Answer · edited May 23 '17 at 12:23

Aha I think there is no way than to use dynamic sql for this. based on the answer here PIVOT in sql 2005

below is the solution for works for me.

Create tables(s) etc

-- Create Sample Data using a Table Varable
create table dbo.Company
(Company_ID int,
 part_ID varchar(30),
 SerialNO varchar(30))

-- Load Sample Data
INSERT INTO Company VALUES (1, 'BAU', 'BAU2512')
INSERT INTO Company VALUES (1, 'BAU', 'BAU3512')
INSERT INTO Company VALUES (1, 'BAU', 'BAU4512')
INSERT INTO Company VALUES (2, 'BAU', 'BAU5512')
INSERT INTO Company VALUES (2, 'BAU', 'BAU6512')
INSERT INTO Company VALUES (2, 'BAU', 'BAU7512')

The query

DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique
DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique

SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + ']'
        ,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [col_' + CONVERT(varchar, PIVOT_CODE) + ']'
FROM (
    SELECT DISTINCT PIVOT_CODE
    FROM (
        SELECT Company_ID, SerialNO, ROW_NUMBER() OVER (PARTITION BY Company_ID ORDER BY SerialNO) AS PIVOT_CODE
        FROM Company
    ) AS rows
) AS PIVOT_CODES

SET @sql = '
;WITH p AS (
    SELECT Company_ID, SerialNO, ROW_NUMBER() OVER (PARTITION BY Company_ID ORDER BY SerialNO) AS PIVOT_CODE
    FROM Company
)
SELECT Company_ID, ' + @select_list + '
FROM p
PIVOT (
    MIN(SerialNO)
    FOR PIVOT_CODE IN (
        ' + @pivot_list + '
    )
) AS pvt
'

PRINT @sql

EXEC (@sql)

Answer here:

http://sqlfiddle.com/#!3/7fd86/1

Well done on finding a solution that works for you! I must say, though, that if you want to have a better chance of getting a quick and helpful answer here you should be more elaborate in describing your problem. For instance, nothing in your question suggests that you want a *dynamic* pivot. In this connection, @CognitiveCarbon's suggestion seems to me perfectly valid, although your own answer makes me suspect you'd already was aware of the PIVOT clause itself. Which leads to another issue with your question, namely, you never said in it what you had tried (or what you'd known couldn't help). — Andriy M, May 14 '12 at 05:38
Please don't take it as a criticism for the sake of criticism. I often find that following the recommendations outlined in the FAQ ([ask]) works for the benefit of all the parties involved. Those asking question are more likely to receive high-quality help in a timely fashion, those answering them may have more fun answering, and those looking for ready answers have more chances of finding what they are looking for. Anyway, it was really nice of you to come back and post what you had ended up with as a solution, thanks. — Andriy M, May 14 '12 at 05:39

score 1 · Answer 2 · answered May 14 '12 at 03:49

1

Looks like you want a PIVOT statement. Take a look here.

answered May 14 '12 at 03:49

CognitiveCarbon

191
7

Thanks for that, the pivot was what I needed in the end, have upvoted your answers :-) – Ricky Gummadi May 14 '12 at 21:20

SQL query to dynamic Pivot/Transform data from table

2 Answers2