how to combine multiple tables to a view 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 2008 Forums

Transact-SQL (2008)

how to combine multiple tables to a view table

Author

Topic

jinma78
Starting Member

5 Posts

Posted - 2012-01-30 : 16:32:24

I want to combine multiple tables and create a view table that has all the information. below is what I want, can you SQL masters show me how I can do this with sql statements?
Thanks in advance.

Subgroup table
Subgrp_id (Primary Key,) Part_id, Process_id
1, Machine, tripple
2, Machine, single
3, Machine, dual

UD table
Subgrp_id , UD_grp, UD_data
(Primary Key)
1, Physical Lane, Lane1
1, Sub Lane, Sublane2
1, Machine Number, 9999
2, Physical Lane, Lane2
2, Sub Lane , Sublane1
2, Machine Number, 8888
3, Physical Lane, Lane1
3, Sub Lane, Sublane3
3, Machine Number, 7777

Test table
Subgrp_id , Test_item , Test_value
(Primary Key)
1, Belt_speed, 40
1, Overall_yield, 99
1, weight, 200
2, Belt_speed, 40
2, Overall_yield, 88
2, weight, 199
3, Belt_speed, 30
3, Overall_yield, 50
3, weight, 100

I need to write a query that will combine above table like below. Where you end up with three rows with lots of columns.

Subgrp_id, Part_id, Process_id, UD_grp, UD_data, Test_data, Test_value……

1 Mahcine, tripple, Physical, lane, Lane1, Sub lane, Sublane2, Machine number, 9999, Belt speed, 40 Overall yield, 99, weight, 200

2 Machine, single, Physical lane, Lane2, Sub lane, Sublane1, Machine number, 8888, Belt speed, 40, Overall yield, 88, weight, 199,

3 Machine, dual, Physical lane, Lane1, Sub lane, Sublane3, Machine number, 7777, Belt speed, 30, Overall yield, 50, weight, 100

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 16:59:10

[code]
SELECT s.*,
STUFF((SELECT ',' + UD_grp + ',' + UD_data FROM UD WHERE Subgrp_id = s.Subgrp_id FOR XML PATH('')),1,1,'') +
(SELECT ',' + Test_item + ',' + Test_value FROM Test WHERE Subgrp_id = s.Subgrp_id FOR XML PATH(''))
FROM Subgroup s
[/code]

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

jinma78
Starting Member

5 Posts

Posted - 2012-01-30 : 17:10:03

thank you so much for your answer. I'll give it a try but can you explain your query? I'm not familiar with STUFF and FOR XML PATH command. thanks

jinma78
Starting Member

5 Posts

Posted - 2012-01-30 : 18:27:37

I've just tried what you suggested and I have more question.

I just put the part_id, process_id, UD_grp,UD_data,Test_item as char, but they are infact integer in my db table. so I had to change your query as follows, but after doing it I get a result but for UD_grp,UD_data, TEST_item, and Test_value, I have no column name for it. and I get the result as xml format. I want the result to have the following column

Subgrp_id, Part_id, Process_id, UD_grp, UD_data, Test_data, Test_value……

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 19:50:12

you mean you want result in seperate columns? then you need to use pivot

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

jinma78
Starting Member

5 Posts

Posted - 2012-01-30 : 22:07:03

then how do I use pivot tables is it like sequel? yes I do needed in separate columns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 00:05:48

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

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

jinma78
Starting Member

5 Posts

Posted - 2012-01-31 : 12:18:08

is Piviot really the only way? when was piviot introduced to SQL? is this something new?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 12:55:11

nope...the method in link can be used from sql 2000 onwards

but you've separate PIVOT operator available which you can use only if you're on 2005 or above

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources