SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Shift Rows to Colums on Condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cyborgnaren
Starting Member

3 Posts

Posted - 01/15/2013 :  13:33:09  Show Profile  Reply with Quote
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 - 01/15/2013 :  14:49:50  Show Profile  Reply with Quote
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
Go to Top of Page

cyborgnaren
Starting Member

3 Posts

Posted - 01/15/2013 :  21:02:32  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/16/2013 :  23:11:25  Show Profile  Reply with Quote

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


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000