Solved

Pivot query with values

Posted on 2013-05-17
2
295 Views
Last Modified: 2013-10-23
Sample TableI have a table combined_list and I would like the output to be a pivot output that shows for each unique entry in column 0, to return for each year in column 1 have each value in column 2

For example

PN                         2007            2008       2009

AK801US#ABA      7F                7F           87
AK802US#ABA                          87          88
0
Comment
Question by:Fairfield
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 39175599
CREATE TABLE SomeTable([Column 0] varchar(50), [Column 1] int, [Column 3] varchar(50))

INSERT INTO SomeTable([Column 0], [Column 1], [Column 3]) VALUES
('AK810US#ABA', 2007, '7F'),
('AK810US#ABA', 2008, '7F'),
('AK810US#ABA', 2009, '87'),
('AK820US#ABA', 2007, '7F'),
('AK820US#ABA', 2008, '7F'),
('AK820US#ABA', 2009, '87'),
('AK830US#ABA', 2010, '87')

DECLARE @sql varchar(MAX) = 'SELECT t1.[Column 0] AS PN'

SELECT @sql = @sql + ', (SELECT MAX(t2.[Column 3])
    FROM SomeTable t2
    WHERE t2.[Column 0] = t1.[Column 0] AND t2.[Column 1] = ' +
    CONVERT(varchar, [Column 1]) + ') AS [' + CONVERT(varchar, [Column 1]) + ']'
FROM SomeTable
GROUP BY [Column 1]
ORDER BY [Column 1]

SET @sql = @sql + ' FROM SomeTable t1
    GROUP BY t1.[Column 0]
    ORDER BY t1.[Column 0]'

EXEC(@sql)

DROP TABLE SomeTable
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 39178698
You can try PIVOT also.
select *
  from SomeTable
pivot (max([Column 3]) for [Column 1] in ([2007],[2008],[2009])) as p

Open in new window


see this example: http://sqlfiddle.com/#!3/2ba3b/1
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question