Shift Rows to Colums on Condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Shift Rows to Colums on Condition

Author

Topic

cyborgnaren
Starting Member

3 Posts

Posted - 2013-01-15 : 13:33:09

SELECT
[SegmentS95Id] AS 'Seg'
,[MatS95Id] as 'Mat'
,[r_Use] AS 'Use'
,[Quantity] AS 'Qty'
FROM S95Definition

I get the below result
Seg Mat Use Qty
1815 FK19929 Con 1
1610 FK19929 Con 1
2400 FK19929 Pro 1
2400 FKFKFK Con 1
1410 FK19929 Con 1
1001 FK19929 Con 1
1210 FK19929 Con 1

I need the below result
Seg Mat-C C-Qty Mat-P P-Qty
1815 FK19929 1 NULL 0
1610 FK19929 1 NULL 0
2400 FK19929 1 FKFKFK 1
1410 FK19929 1 NULL 0
1001 FK19929 1 NULL 0
1210 FK19929 1 NULL 0

Any Help ?

Robowski
Posting Yak Master

101 Posts

Posted - 2013-01-15 : 14:49:50

quote:
Originally posted by cyborgnaren

SELECT
[SegmentS95Id] AS 'Seg'
,[MatS95Id] as 'Mat'
,[r_Use] AS 'Use'
,[Quantity] AS 'Qty'
FROM S95Definition

I get the below result
Seg Mat Use Qty
1815 FK19929 Con 1
1610 FK19929 Con 1
2400 FK19929 Pro 1
2400 FKFKFK Con 1
1410 FK19929 Con 1
1001 FK19929 Con 1
1210 FK19929 Con 1

I need the below result
Seg Mat-C C-Qty Mat-P P-Qty
1815 FK19929 1 NULL 0
1610 FK19929 1 NULL 0
2400 FK19929 1 FKFKFK 1
1410 FK19929 1 NULL 0
1001 FK19929 1 NULL 0
1210 FK19929 1 NULL 0

Any Help ?

The second result has more columns and isn't using all the fields from S95Definitions I.E 'Use'

Are you trying to get results from two tables? If so you need to join them MatS95ID look to be the same in both?

What is the other table? Where it is NULL and FKFKF are they displayed like that in the second table? you could sue a case statement for your select otherwise...

something like:

SELECT
[SegmentS95Id] AS 'Seg'
,[MatS95Id] as 'Mat'
,'Mat-P' = CASE
WHEN r_use = 'CON' THEN NULL ELSE 'FKFKFK' END
FROM S95Definition

cyborgnaren
Starting Member

3 Posts

Posted - 2013-01-15 : 21:02:32

Its a joined table , I have not indicated for simplicity.
The result set is what is displayed in select. The MatDef_P and MatDef_C and Qty_P and Qty_C are columns that i additionally need from the rwo sets : MatDef and Qty , which would hold values for Con(MatDef_C) and Pro(MatDef_P) along with corresponding values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-16 : 23:11:25

[code]
SELECT Seg,
MAX(CASE WHEN Use='Con' THEN Mat END) AS [Mat-C],
MAX(CASE WHEN Use='Con' THEN Qty END) AS [C-Qty],
MAX(CASE WHEN Use='Pro' THEN Mat END) AS [Mat-P],
MAX(CASE WHEN Use='Pro' THEN Qty END) AS [P-Qty]
FROM
(Yourquery) q
GROUP BY Seg
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources