Need help in Queue between two table

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)

Need help in Queue between two table

Author

Topic

kenlok
Starting Member

16 Posts

Posted - 2009-06-12 : 21:17:37

create table #TempColorSize (ID varchar(8),Size1 varchar(8),Size2 varchar(8),Size3 varchar(8),Size4 varchar(8),Size5 varchar(8),Size6 varchar(8),Size7 varchar(8),Size8 varchar(8))
insert #TempColorSize
select 'ID0001','3M','6M','9M','12M','15M','18M','21M','24M'

create table #TempColorSizeQty (ID varchar(8),Size1 varchar(8),Size2 varchar(8),Size3 varchar(8),Size4 varchar(8),Size5 varchar(8),Size6 varchar(8),Size7 varchar(8),Size8 varchar(8), Qty1 decimal(14,3), Qty2 decimal(14,3), Qty3 decimal(14,3), Qty4 decimal(14,3),Qty5 decimal(14,3), Qty6 decimal(14,3), Qty7 decimal(14,3), Qty8 decimal(14,3))
insert #TempColorSizeQty
select 'ID0001','12M','15M','18M','21M','24M','3M','6M','9M', 4,5,6,7,8,1,2,3

-----------------------------------------------------------------------------------------------

I would like the result to be display the size seq order by #TempColorSize Size.

ID Size1 Size2 Size3 Size4 Size5 Size6 Size7 Size8 Qty1 Qty2 Qty3 Qty4 Qty5 Qty6 Qty7 Qty8
ID0001 3M 6M 9M 12M 15M 18M 21M 24M 1.000 2.000 3.000 4.000 5.000 6.000 7.000 8.000

How can I do that?
Please help!!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-13 : 02:19:51

Just put a join

select a.size1,a.size2.....,b.qty1,b.qty2.... from #TempColorSize a ,#TempColorSizeQty b
where a.id=b.id

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-13 : 02:57:51

quote:
Originally posted by senthil_nagore

Just put a join

select a.size1,a.size2.....,b.qty1,b.qty2.... from #TempColorSize a ,#TempColorSizeQty b
where a.id=b.id

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Make use of ANSI joins

select a.size1,a.size2.....,b.qty1,b.qty2.... from #TempColorSize a inner join #TempColorSizeQty b
on a.id=b.id

Madhivanan

Failing to plan is Planning to fail

kenlok
Starting Member

16 Posts

Posted - 2009-06-13 : 05:19:39

I have test the inter join

select a.ID, a.Size1, a.Size2, a.Size3, a.Size4, a.Size5, a.Size6, a.Size7, a.Size8, b.Qty1, b.Qty2, b.Qty3, b.Qty4, b.Qty5,b.Qty6, b.Qty7, b.Qty8 from #TempColorSize a inner join #TempColorSizeQty b
on a.ID=b.ID

However, I would like to display Qty1, Qty2, Qty3.... is also same as the size seq

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-13 : 09:23:04

you should normalize your table

else this is the only method that come to my mind right now . . . with an un-normalize table. . . it will not be easy


SELECT	c.*, d.[1] AS Qty1, d.[2] AS Qty2, d.[3] AS Qty3, d.[4] AS Qty4, d.[5] AS Qty5, d.[6] AS Qty6, d.[7] AS Qty7, d. AS Qty8
FROM	#TempColorSize c
INNER JOIN
(
	SELECT	*
	FROM
	(
		SELECT	c.ID, t.Qty, c.Seq
		FROM
		(
			SELECT	ID, Size = Size1, Seq = 1
			FROM	#TempColorSize			UNION ALL
			SELECT	ID, Size = Size2, Seq = 2
			FROM	#TempColorSize			UNION ALL
			SELECT	ID, Size = Size3, Seq = 3
			FROM	#TempColorSize			UNION ALL
			SELECT	ID, Size = Size4, Seq = 4
			FROM	#TempColorSize			UNION ALL
			SELECT	ID, Size = Size5, Seq = 5
			FROM	#TempColorSize			UNION ALL
			SELECT	ID, Size = Size6, Seq = 6
			FROM	#TempColorSize			UNION ALL
			SELECT	ID, Size = Size7, Seq = 7
			FROM	#TempColorSize			UNION ALL
			SELECT	ID, Size = Size8, Seq = 8
			FROM	#TempColorSize
		) c
		INNER JOIN
		(
			SELECT	ID, Size = Size1, Qty = Qty1
			FROM	#TempColorSizeQty	UNION ALL
			SELECT	ID, Size = Size2, Qty = Qty2
			FROM	#TempColorSizeQty	UNION ALL
			SELECT	ID, Size = Size3, Qty = Qty3
			FROM	#TempColorSizeQty	UNION ALL
			SELECT	ID, Size = Size4, Qty = Qty4
			FROM	#TempColorSizeQty	UNION ALL
			SELECT	ID, Size = Size5, Qty = Qty5
			FROM	#TempColorSizeQty	UNION ALL
			SELECT	ID, Size = Size6, Qty = Qty6
			FROM	#TempColorSizeQty	UNION ALL
			SELECT	ID, Size = Size7, Qty = Qty7
			FROM	#TempColorSizeQty	UNION ALL
			SELECT	ID, Size = Size8, Qty = Qty8
			FROM	#TempColorSizeQty
		) t	ON	c.ID	= t.ID
			AND	c.Size	= t.Size
	) d
	pivot
	(
		SUM(Qty)
		FOR	Seq	IN ([1], [2], [3], [4], [5], [6], [7], )
	) p
) d	ON	c.ID	= d.ID

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources