create table. - 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

create table.

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-15 : 06:38:37

I have 4 query for different calucation purpose with contain of different where condition and result create different table on
3 column fields.

query 1.
table A - sku, adj_in_qty, dept where x

query 2
table B - sku, adj_out_qty , dept where xy

query 3
table C - sku, delivery_in_qty, dept where xyz

query 4 - sku, delivery_out_qty , dept where xyz0
table D

How to get 4 query of result into a single one E table , pls give a sample query ?

gopalrwt77
Starting Member

3 Posts

Posted - 2014-10-15 : 08:03:58

Please elaborate your question. Do you wish to create the table using the variables or want to retrieve information from the table?

gopal

gopalrwt77
Starting Member

3 Posts

Posted - 2014-10-15 : 08:06:24

This query might help.... assuming

Select sku, delivery_out_qty , dept where 'variable' in(x, xy, xyz, xyz0)

gopal

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-10-15 : 11:30:25

-- create temp table
Create Table #SKUData
(
sku varchar(50) not null primary key,
adj_in_qty int null,
adj_out_qty int null,
delivery_in_qty int null,
delivery_out_qty int null,
dept varchar(50)
)
-- insert some values
Insert Into #SKUData
Values
(12345, 22, 29, 23, 35, 'x'),
(12346, 15, 21, 21, 33, 'xy'),
(12347, 19, 24, 22, 42, 'xyz'),
(12348, 11, 33, 25, 38, 'xyz0')
Go

-- query data
with filter
As
(
Select
TableA = Case
When dept = 'x' Then [delivery_out_qty]
Else 0
End,
TableB = Case
When dept = 'xy' Then [adj_out_qty]
Else 0
End,
TableC = Case
When dept = 'xyz' Then [delivery_in_qty]
Else 0
End,
TableD = Case
When dept = 'xyz0' Then [delivery_out_qty]
Else 0
End
From #SKUData
)
Select
Sum(TableA) as TableA,
Sum(TableB) as TableB,
Sum(TableC) as TableC,
Sum(TableD) as TableD

From filter

-- clean up

Drop Table #SKUData
Go

We are the creators of our own reality!

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-16 : 00:39:11

I want to run of 4 query from different tables and get the 4 query result can put into one single new table . Please give advice.

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-16 : 00:41:54

query 1 is calucate sku of stock adjust qty
query 2 is calucate sku of stock transfer qty
query 3 is calucate sku of opening on hand qty
query 4 is calucate sku of return good qty

new table can store of stock qty, transfer qty, on hand qty, return gty by sku.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-16 : 00:44:53

Use UNION ALL, provided you have similar table structure for all the five tables as below:

insert into table5(col1, col2, col3)
select  sku, adj_in_qty, dept from table1 where x
union all
select sku, adj_out_qty , dept from table2 where xy
union all
select sku, delivery_in_qty, dept from table3 where xyz
union all
select sku, delivery_out_qty , dept from table4 where xyz0

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-16 : 04:20:16

if used of UNION ALL is it possible automatic to matching the sku ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-16 : 04:28:37

I didn't get your question. Could you please rephrase?

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

usafelix
Posting Yak Master

165 Posts

Posted - 2014-10-16 : 05:40:38

insert into table5(col1, col2, col3)
select sku, adj_in_qty, dept from table1 where x
union all
select sku, adj_out_qty , dept from table2 where xy
union all
select sku, delivery_in_qty, dept from table3 where xyz
union all
select sku, delivery_out_qty , dept from table4 where xyz0
======================================================
I am try to follow above information to edit my coding in below but wrong message.

select sku, adj_in_qty, dept from table1 where x
union all
select sku, adj_out_qty , dept from table2 where xy
union all
select sku, delivery_in_qty, dept from table3 where xyz
union all
select sku, delivery_out_qty , dept from table4 where xyz0

insert into table5(sku, adj_in_qty, adj_out_qty,delivery_in_qty,delivery_out_qty)

aniruddhaa
Starting Member

3 Posts

Posted - 2014-10-16 : 07:44:11

You are doing this all incorrect way..

1. If you want to insert data from 4 different queries into one single table,
then you need to name the columns with a common name

insert into table5(sku, adj_in_qty, dept)
SELECT sku,adj_in_qty, dept FROM
(select sku, adj_in_qty, dept from table1 where x
union all
select sku, adj_out_qty , dept from table2 where xy
union all
select sku, delivery_in_qty as adj_out_qty, dept from table3 where xyz
union all
select sku, delivery_out_qty as adj_out_qty , dept from table4 where xyz0) ABC

If this is not the requirement, then better create two different tables, one for adj_out_qty and another for delivery_in_qty

In case your need is to put this data in a table with different columns then you need to PIVOT/UNPIVOT

Best,
Aniruddha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources