Need help when using Union - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Need help when using Union

Author

Topic

Harhour
Starting Member

3 Posts

Posted - 2010-10-31 : 05:24:23

hI ALL

this i my statement

select item_code,order_id ,wk1_no,wk2_no,wk3_no,wk4_no,wk5_no,wk6_no,wk7_no,wk8_no,idwk from kdr_order_dtl
where order_id='2010_10_order_405'

i get 24 rows

item_code order_id wk1_no wk2_no wk3_no wk4_no wk5_no wk6_no wk7_no wk8_no idwk
002K7X1 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2275
002K7X1 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2276
002K7X1 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2277
002K7X1 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2278
002K7X1 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2279
002K7X1 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2280
002K7X1 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2281
002K7X1 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2282
002L0G7 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2283
002L0G7 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2284
002L0G7 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2285
002L0G7 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2286
002L0G7 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2287
002L0G7 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2288
002L0G7 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2289
002L0G7 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2290
002M7D8 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2291
002M7D8 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2292
002M7D8 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2293
002M7D8 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2294
002M7D8 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2295
002M7D8 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2296
002M7D8 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2297
002M7D8 2010_10_Order_405 week47 week48 week49 week50 week51 week52 week1 week2 2298

I used union to transform columns wk1_no wk2_no wk3_no wk4_no wk5_no wk6_no wk7_no wk8_no into rows and have the column name Wk NB

SELECT DISTINCT TOP (100) PERCENT Item_code, Order_id, Wk1_no AS [WK NB]
FROM KDR_Order_Dtl
WHERE (Order_id = '2010_10_order_405')
UNION ALL
SELECT DISTINCT TOP (100) PERCENT Item_code, Order_id, Wk2_no
FROM KDR_Order_Dtl AS KDR_Order_Dtl_7
WHERE (Order_id = '2010_10_order_405')
UNION ALL
SELECT DISTINCT TOP (100) PERCENT Item_code, Order_id, Wk3_no
FROM KDR_Order_Dtl AS KDR_Order_Dtl_6
WHERE (Order_id = '2010_10_order_405')
UNION ALL
SELECT DISTINCT TOP (100) PERCENT Item_code, Order_id, Wk4_no
FROM KDR_Order_Dtl AS KDR_Order_Dtl_5
WHERE (Order_id = '2010_10_order_405')
UNION ALL
SELECT DISTINCT TOP (100) PERCENT Item_code, Order_id, Wk5_no
FROM KDR_Order_Dtl AS KDR_Order_Dtl_4
WHERE (Order_id = '2010_10_order_405')
UNION ALL
SELECT DISTINCT TOP (100) PERCENT Item_code, Order_id, Wk6_no
FROM KDR_Order_Dtl AS KDR_Order_Dtl_3
WHERE (Order_id = '2010_10_order_405')
UNION ALL
SELECT DISTINCT TOP (100) PERCENT Item_code, Order_id, Wk7_no
FROM KDR_Order_Dtl AS KDR_Order_Dtl_2
WHERE (Order_id = '2010_10_order_405')
UNION ALL
SELECT DISTINCT TOP (100) PERCENT Item_code, Order_id, Wk8_no
FROM KDR_Order_Dtl AS KDR_Order_Dtl_1
WHERE (Order_id = '2010_10_order_405')
ORDER BY Item_code

I am getting 24 rows

Item_code Order_id WK NB
002K7X1 2010_10_Order_405 week1
002K7X1 2010_10_Order_405 week2
002K7X1 2010_10_Order_405 week47
002K7X1 2010_10_Order_405 week48
002K7X1 2010_10_Order_405 week49
002K7X1 2010_10_Order_405 week50
002K7X1 2010_10_Order_405 week51
002K7X1 2010_10_Order_405 week52
002L0G7 2010_10_Order_405 week1
002L0G7 2010_10_Order_405 week2
002L0G7 2010_10_Order_405 week47
002L0G7 2010_10_Order_405 week48
002L0G7 2010_10_Order_405 week49
002L0G7 2010_10_Order_405 week50
002L0G7 2010_10_Order_405 week51
002L0G7 2010_10_Order_405 week52
002M7D8 2010_10_Order_405 week1
002M7D8 2010_10_Order_405 week2
002M7D8 2010_10_Order_405 week47
002M7D8 2010_10_Order_405 week48
002M7D8 2010_10_Order_405 week49
002M7D8 2010_10_Order_405 week50
002M7D8 2010_10_Order_405 week51
002M7D8 2010_10_Order_405 week52

Here is my problem i want to add the column IDWK from the first statement to the second statement (Union) and to get 24 rows

i want like this for order 2010_10_Order_405

Item_code Order_id WK NB IDWK
002K7X1 2010_10_Order_405 week1 2275
002K7X1 2010_10_Order_405 week2 2276
002K7X1 2010_10_Order_405 week47 2278
002K7X1 2010_10_Order_405 week48 2279
002K7X1 2010_10_Order_405 week49 2280
002K7X1 2010_10_Order_405 week50 2281
002K7X1 2010_10_Order_405 week51 2282
002K7X1 2010_10_Order_405 week52 2283
002L0G7 2010_10_Order_405 week1 2284
002L0G7 2010_10_Order_405 week2 2285
002L0G7 2010_10_Order_405 week47 2286
002L0G7 2010_10_Order_405 week48 2287
002L0G7 2010_10_Order_405 week49 2288
002L0G7 2010_10_Order_405 week50 2289
002L0G7 2010_10_Order_405 week51 2290
002L0G7 2010_10_Order_405 week52 2291
002M7D8 2010_10_Order_405 week1 2292
002M7D8 2010_10_Order_405 week2 2293
002M7D8 2010_10_Order_405 week47 2294
002M7D8 2010_10_Order_405 week48 2295
002M7D8 2010_10_Order_405 week49 2296
002M7D8 2010_10_Order_405 week50 2297
002M7D8 2010_10_Order_405 week51 2298
002M7D8 2010_10_Order_405 week52 2299

and then i want the same output but for all order numbers more tan 24 rows..

thanks in return

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-31 : 08:37:54

Have you considered the new UNPIVOT operator?


SELECT	u.Item_Code,
	u.Order_ID,
	u.WeekNumber,
	u.IdWk
FROM	dbo.kdr_order_dtl AS s
UNPIVOT	(
		WeekNumber
		FOR theCol IN (s.wk1_no, s.wk2_no, s.wk3_no, s.wk4_no, s.wk5_no, s.wk6_no, s.wk7_no, s.wk8_no)
	) AS u

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources