CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Matthew Owens
Matthew Owens

Reputation: 1

T-SQL pivot guidance

I have a table function in t-sql that returns a split value in 3 entries against a job number. So for instance a typical return would be 

JobNumber SplitValue
J1         X1
J1         X2
J1         X3
J2         X1
J2         X2
J2         X3

in row returns.

However, what i'd like to do is pivot the result set so I have 

J1 X1 X2 X3 
J2 X1 X2 X3

Can anyone help?

Cheers

Upvotes: 0

Views: 457

Answers (4)

Andrey Morozov
Andrey Morozov

Reputation: 7979

Try this:

Sample data

select * into #tmp from (
    select 'J1' [JobNumber], 'X1' [SplitValue] union all
    select 'J1', 'X2' union all
    select 'J1', 'X3' union all
    select 'J2', 'X1' union all
    select 'J2', 'X2' union all
    select 'J2', 'X3') as t;

JobNumber SplitValue
J1         X1
J1         X2
J1         X3
J2         X1
J2         X2
J2         X3

Query

select [JobNumber], [X1] [col1], [X2] [col2], [X3] [col3]
from #tmp
pivot (
    max([SplitValue])
    for [SplitValue] in ([X1], [X2], [X3])
) as t

JobNumber   col1    col2    col3
J1          X1      X2      X3
J2          X1      X2      X3

SQL Fiddle

Upvotes: 0

Brent D
Brent D

Reputation: 908

Pivot tables can be a little hard to use at first, but for simple sets of data they can be pretty powerful.

Select [J1],[J2],...
from (
 select JobNumber, SplitValue from tablename
) as sourcetable
PIVOT (
 Max(SplitValue) for JobNumber in ([J1],[J2],...)
) as PivotTable

Unfortunately, this also means you must hardcode the columns if not using Dynamic SQL. Check out the MSDN reference on Pivot and Unpivot tables.

Upvotes: 2

Phil Gan
Phil Gan

Reputation: 2863

This is my solution to your problem that I just hacked up. No comments or anything I'm afraid, but hopefully this gives you a starting point.

With a starting table #Jobs like this:

JobNumber SplitValue
--------- ----------
J1        X1
J1        X2
J1        X3
J2        X1
J2        X2
J2        X3

You get an output of this:

JobNumber col_0 col_1 col_2
--------- ----- ----- -----
J1        X1    X2    X3
J2        X1    X2    X3

Code:

CREATE TABLE #Jobs
(
    JobNumber varchar(2),
    SplitValue varchar(2)
)

INSERT INTO #Jobs (JobNumber, SplitValue) VALUES ('J1','X1')
INSERT INTO #Jobs (JobNumber, SplitValue) VALUES ('J1','X2')
INSERT INTO #Jobs (JobNumber, SplitValue) VALUES ('J1','X3')
INSERT INTO #Jobs (JobNumber, SplitValue) VALUES ('J2','X1')
INSERT INTO #Jobs (JobNumber, SplitValue) VALUES ('J2','X2')
INSERT INTO #Jobs (JobNumber, SplitValue) VALUES ('J2','X3')

SELECT * FROM #Jobs

DECLARE @ColumnsNeeded int

;WITH CountsTable AS
(
    SELECT COUNT(*) AS ColumnCount FROM #Jobs GROUP BY JobNumber
)
SELECT @ColumnsNeeded = MAX(ColumnCount) FROM CountsTable

DECLARE @Counter int
SET @Counter = 0

CREATE TABLE #Pivoted
(
    JobNumber varchar(2)
)
DECLARE @AddColumnCommandPre varchar(255)
DECLARE @AddColumnCommandPost varchar(255)
DECLARE @AddColumnCommand varchar(255)

SET @AddColumnCommandPre = 'ALTER TABLE #Pivoted ADD col_'
SET @AddColumnCommandPost = ' varchar(2)'

WHILE (@Counter < @ColumnsNeeded)
BEGIN
    SET @AddColumnCommand = @AddColumnCommandPre + CAST(@Counter as varchar(3)) + @AddColumnCommandPost
EXECUTE (@AddColumnCommand)
    SELECT @Counter = @Counter + 1
END

SELECT * INTO #JobsWithRowNum FROM #Jobs
ALTER TABLE #JobsWithRowNum ADD RowNum int IDENTITY(1,1)

CREATE CLUSTERED INDEX JobsWithRowNum_RowNum ON #JobsWithRowNum(RowNum)

DECLARE @RowCount int
SELECT @RowCount = COUNT(*) FROM #JobsWithRowNum

DECLARE @RowInsert varchar(255), @Columns varchar(255), @Data varchar(255)
DECLARE @LastJobNumber varchar(3), @CurrentJobNumber varchar(3)
DECLARE @ColumnCounter int

SET @RowInsert = 'INSERT INTO #Pivoted (JobNumber'
SET @Columns = ''
SELECT @LastJobNumber = JobNumber FROM #JobsWithRowNum WHERE RowNum = 1
SET @Data = ') VALUES (''' + @LastJobNumber

SET @Counter = 1
SET @ColumnCounter = 0
WHILE (@Counter <= @RowCount)
BEGIN
    SELECT @CurrentJobNumber = JobNumber FROM #JobsWithRowNum WHERE RowNum = @Counter
    IF @CurrentJobNumber <> @LastJobNumber
    BEGIN
        EXEC (@RowInsert + @Columns + @Data + ''')')
        SET @Data = ') VALUES (''' + @CurrentJobNumber
        SET @LastJobNumber = @CurrentJobNumber
        SET @ColumnCounter = 0
        SET @Columns = ''
    END

    SET @Columns = @Columns + ', col_' + CAST(@ColumnCounter AS varchar(3))
    SELECT @Data = @Data + ''', ''' + SplitValue FROM #JobsWithRowNum WHERE RowNum = @Counter

    SET @Counter = @Counter + 1
    SET @ColumnCounter = @ColumnCounter + 1
END

EXEC (@RowInsert + @Columns + @Data + ''')')

SELECT * FROM #Pivoted

DROP TABLE #JobsWithRowNum
DROP TABLE #Pivoted
DROP TABLE #Jobs

Upvotes: 0

Robert Kaucher
Robert Kaucher

Reputation: 1861

Your data sample is kind of hard to understand as it looks really generic. As Tim stated the version of SQL Server would be helpful.

Here is a version agnostic query that provides the requested output:

    DECLARE @A Table
(JobNumber varchar(2), SplitValue varchar(2))

INSERT INTO @A
SELECT 'J1', 'X1' UNION ALL
SELECT 'J1',  'X2' UNION ALL
SELECT 'J1',  'X3' UNION ALL
SELECT 'J2',  'X1' UNION ALL
SELECT 'J2', 'X2' UNION ALL
SELECT 'J2', 'X3'    
    SELECT JobNumber
        ,(SELECT 'X1') as [X1]
        ,(SELECT 'X2') as [X2]
        ,(SELECT 'X3') as [X3]
    FROM @A
    GROUP BY JobNumber

But I'm not convinced it will be of much real world help except to show you how to build a pivot using subqueries. Better data = better example.

Upvotes: 0

Related Questions