Arranging row values in order - Pls Help

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 2005 Forums

Transact-SQL (2005)

Arranging row values in order - Pls Help

Author

Topic

mummy
Starting Member

9 Posts

Posted - 2009-03-18 : 06:12:02

Hi,

I have a table with the below values:

quote:

Item_|Activity_|Code_|_Head___|Parent_|_Type_|_Status_|Curr_|_Price_|
---------------------------------------------------------------------
0____|_ Main__|_CVL__|__1_____|_0_____|_DV20_|_Normal_|_USD_|___0___|
113__|__Main__|_CVL__|__1_____|_0_____|_DV40_|_Normal_|_USD_|___0___|
19___|__Sub___|_STV__|__1.1___|_1_____|_DV20_|_Normal_|_INR_|___2288|
131__|__Sub___|_STV__|__1.1___|_1_____|_DV40_|_Normal_|_INR_|___3432|
1____|__Sub___|_MIX__|__1.131_|_1_____|_DV20_|_Normal_|_INR_|___400_|
114__|__Sub___|_MIX__|__1.131_|_1_____|_DV40_|_Normal_|_INR_|___800_|
20___|__Main__|_TLD__|__2_____|_0_____|_DV20_|_Normal_|_USD_|___0___|
132__|__Main__|_TLD__|__2_____|_0_____|_DV40_|_Normal_|_USD_|___0___|
23___|__Sub___|_STV__|__2.1___|_2_____|_DV20_|_Normal_|_INR_|___1320|
135__|__Sub___|_STV__|__2.1___|_2_____|_DV40_|_Normal_|_INR_|___1980|
29___|__Sub___|_HST__|__2.25.9|_2.25__|_DV20_|_Normal_|_INR_|___1700|
141__|__Sub___|_HST__|__2.25.9|_2.25__|_DV40_|_Normal_|_INR_|___2550|

I would like to show the above details as below as my result:

Code...|..Head..|..PRICE_DV20..|..PRICE_DV40..|..Curr..|
--------------------------------------------------------
CVL____|__1_____|____0_________|_____0________|__USD___|
STV____|__1.1___|____2288______|_____3432_____|__INR___|
MIX____|__1.131_|____400_______|_____800______|__INR___|
TLD____|__2_____|____0_________|_____0________|__USD___|
STV____|__2.1___|____1320______|_____1980_____|__INR___|
HST____|__2.25.9|____1700______|_____2550_____|__INR___|

CAN ANYONE HELP ME PLEASE TO GET THE RESULT AS ABOVE. THANKS.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 06:24:28

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Item INT,
Activity VARCHAR(20),
Code VARCHAR(20),
Head VARCHAR(20),
Parent VARCHAR(20),
Type VARCHAR(20),
Status VARCHAR(20),
Curr VARCHAR(20),
Price INT
)

INSERT @Sample
SELECT 0, 'Main', 'CVL', '1' , '0' , 'DV20', 'Normal', 'USD', 0 UNION ALL
SELECT 113, 'Main', 'CVL', '1' , '0' , 'DV40', 'Normal', 'USD', 0 UNION ALL
SELECT 19, 'Sub' , 'STV', '1.1' , '1' , 'DV20', 'Normal', 'INR', 2288 UNION ALL
SELECT 131, 'Sub' , 'STV', '1.1' , '1' , 'DV40', 'Normal', 'INR', 3432 UNION ALL
SELECT 1, 'Sub' , 'MIX', '1.131' , '1' , 'DV20', 'Normal', 'INR', 400 UNION ALL
SELECT 114, 'Sub' , 'MIX', '1.131' , '1' , 'DV40', 'Normal', 'INR', 800 UNION ALL
SELECT 20, 'Main', 'TLD', '2' , '0' , 'DV20', 'Normal', 'USD', 0 UNION ALL
SELECT 132, 'Main', 'TLD', '2' , '0' , 'DV40', 'Normal', 'USD', 0 UNION ALL
SELECT 23, 'Sub' , 'STV', '2.1' , '2' , 'DV20', 'Normal', 'INR', 1320 UNION ALL
SELECT 135, 'Sub' , 'STV', '2.1' , '2' , 'DV40', 'Normal', 'INR', 1980 UNION ALL
SELECT 29, 'Sub' , 'HST', '2.25.9', '2.25', 'DV20', 'Normal', 'INR', 1700 UNION ALL
SELECT 141, 'Sub' , 'HST', '2.25.9', '2.25', 'DV40', 'Normal', 'INR', 2550

-- Show the expected output
SELECT Code,
Head,
MAX(CASE WHEN Type = 'DV20' THEN Price ELSE 0 END) AS PRICE_DV20,
MAX(CASE WHEN Type = 'DV40' THEN Price ELSE 0 END) AS PRICE_DV40,
Curr
FROM @Sample
GROUP BY Code,
Head,
Curr
ORDER BY Code,
Head,
Curr[/code]

E 12°55'05.63"
N 56°04'39.26"

mummy
Starting Member

9 Posts

Posted - 2009-03-18 : 06:34:02

Thank you so much for your assistance. Its working fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources