February 22, 2012 at 11:52 am
The simple version of my problem is with two tables.
I have two different tables. one is a header and one is details . I need the rows to contain the header and the coulmns to contain the details.
Example
The able I have now is:
SELECT t1.OrderNum, t1.custName, t1.Orderdate, t2.LineItem, t2.Price, t2.deliverymethod
from t1,t2
and the results looks like
ORDERNUM--|--CUSTNAME--|--ORDERDATE--|--LINEITEM---|--PRICE--|--DELIVERY--|--SHIPDATE
ORD1--------------Cust1--------------1/1/2012--------------LI1--------------$100--------------UPS--------2/1/2012
ORD1--------------Cust1--------------1/1/2012--------------LI2--------------$200--------------UPS--------2/1/2012
ORD2--------------Cust2--------------1/5/2012--------------LI1--------------$100--------------FedEx------No
ORD3--------------Cust3--------------1/8/2012--------------LI1--------------$100--------------FedEx------No
ORD3--------------Cust3--------------1/8/2012--------------LI2--------------$200--------------FedEx------No
ORD3--------------Cust3--------------1/8/2012--------------LI3--------------$100--------------FedEx------2/20/2012
..
but what I want is a select statment to give me the table like this
ORDERNUM--|--CUSTNAME--|--ORDERDATE--|--LINEITEM1---|--LINEITEM2---|--LINEITEM3---|--LINEITEM4---|--LINEITEM5
ORD1---------------Cust1---------------1/1/2012-----------------LI1-----------------LI2
ORD2---------------Cust2---------------1/5/2012-----------------LI1
ORD3---------------Cust3---------------1/8/2012-----------------LI1-----------------LI2-----------------LI3
..
and how can I caputer the rest of the data? in this new table?
I am thinking of SELECT t1.OrderNum, t1.custName, t1.Orderdate, (SELECTt2.LineItem FROM t2) from t1
but the sub query is returing many items so its giving me an error
(Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)
February 22, 2012 at 1:41 pm
I just now realized what it was you were trying to accomplish...why are you wanting to do this within SQL and not in a report or sorts?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 22, 2012 at 1:59 pm
Quick way is to write a function that returns a string concatenating the line items and display as one column, whose header is "Line Item 1 , Line Item2, etc.".
The other way is to us a bunch of full joins (on Order ID) to pivot the line items out as columns.
This kind of code is icky but boiler-plate/cut-paste-edit.
You can also try the cross apply operators.
William
February 22, 2012 at 2:28 pm
Ray_NA (2/22/2012)
The simple version of my problem is with two tables.I have two different tables. one is a header and one is details . I need the rows to contain the header and the coulmns to contain the details.
Example
The able I have now is:
SELECT t1.OrderNum, t1.custName, t1.Orderdate, t2.LineItem, t2.Price, t2.deliverymethod
from t1,t2
and the results looks like
ORDERNUM--|--CUSTNAME--|--ORDERDATE--|--LINEITEM---|--PRICE--|--DELIVERY--|--SHIPDATE
ORD1--------------Cust1--------------1/1/2012--------------LI1--------------$100--------------UPS--------2/1/2012
ORD1--------------Cust1--------------1/1/2012--------------LI2--------------$200--------------UPS--------2/1/2012
ORD2--------------Cust2--------------1/5/2012--------------LI1--------------$100--------------FedEx------No
ORD3--------------Cust3--------------1/8/2012--------------LI1--------------$100--------------FedEx------No
ORD3--------------Cust3--------------1/8/2012--------------LI2--------------$200--------------FedEx------No
ORD3--------------Cust3--------------1/8/2012--------------LI3--------------$100--------------FedEx------2/20/2012
..
but what I want is a select statment to give me the table like this
ORDERNUM--|--CUSTNAME--|--ORDERDATE--|--LINEITEM1---|--LINEITEM2---|--LINEITEM3---|--LINEITEM4---|--LINEITEM5
ORD1---------------Cust1---------------1/1/2012-----------------LI1-----------------LI2
ORD2---------------Cust2---------------1/5/2012-----------------LI1
ORD3---------------Cust3---------------1/8/2012-----------------LI1-----------------LI2-----------------LI3
..
and how can I caputer the rest of the data? in this new table?
I am thinking of SELECT t1.OrderNum, t1.custName, t1.Orderdate, (SELECTt2.LineItem FROM t2) from t1
but the sub query is returing many items so its giving me an error
(Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)
You are going to need a dynamic pivot, this is a nontrivial exercise... I'm sure some of the experts can generate a script that might help you.
February 22, 2012 at 3:11 pm
Thank you guys for your responces.
The real life application for this to to do a prcentage complete on orders.
I know that the data is there. Its just presenting them in the form that my management is demanding.
The same will apply to our manufacturing tables.
I have table1 with the WorkOrders headers, and table2 with the router steps for each order along with its start and finish dates. my management want a report with cross table with the first columns as work order numbers and some other details from the WO header table, then the rest of the columns to list the different steps of making each Work Order. The ultimate goal is to show how many steps are done and and how many left to be done.
WO#--|--WODate--|--PartName--|--Step1--|--Step2--|--Step3--|--Step4--|--Step5...Step100
WO1--|--1/1/2012-|--test part1--|--done--|--done--|--done
WO2--|--1/1/2012-|--test part1--|--done--|--done--|--done--|--done--|--done
WO3--|--1/1/2012-|--test part1--|--done--|--done
WO4--|--1/1/2012-|--test part1--|--done--|--done
I tried to do it in excel pivot tables but it didnt work. that's why I tried SQL.
Please Help. 🙂
February 22, 2012 at 3:13 pm
MyDoggieJessie (2/22/2012)
I just now realized what it was you were trying to accomplish...why are you wanting to do this within SQL and not in a report or sorts?
MyDoggieJessie, I tried to do it in excel pivot table but it didnt work. Can you tell me how I can do it in a report?
February 22, 2012 at 3:25 pm
The only ways I can think of accomplishing this is to 1) do it in a reporting services report or 2) use temp tables
1) Create a reporting services report (or have your BI team create one for you), this is by far the easiest solution, works well, not difficult, and beats method #2 hands down
2) Use a few temp tables and a WHILE LOOP to loop through the Header and Detail to build out each record. Of course you'd have to determine the max number of line items for any given order detail and insert the data into a different temp table during each past of the loop for the items.
Prolly take a bit of time to put that one together...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 22, 2012 at 4:02 pm
MyDoggieJessie (2/22/2012)
The only ways I can think of accomplishing this is to 1) do it in a reporting services report or 2) use temp tables1) Create a reporting services report (or have your BI team create one for you), this is by far the easiest solution, works well, not difficult, and beats method #2 hands down
...
well, I am all the BI team and you can tell that I dont know much. How can I go about doing a reporting services report ?
February 22, 2012 at 5:18 pm
Here's some sample code that does what I was referring to earlier using full outer joins on the order id.
This transforms the data similarly to what you've displayed. It generates a table like below. --William
oidcidodtline1shipsdtline2shipsdtline3shipsdt
ord1cst12011-01-011usps 2011-01-112usps 2011-01-123usps NULL
ord2cst22011-02-021fedexNULL2fedex2002-02-02NULLNULLNULL
ord3cst22011-03-021ups 2003-03-03NULLNULLNULLNULLNULLNULL
ord4cst32011-04-031dhl NULL2dhl 2004-04-01NULLNULLNULL
ord5cst12011-05-011rapidNULLNULLNULLNULLNULLNULLNULL
----------------
create table #m --master
(
oid char(4) not null,
cid char(4) not null,
odt date not null
)
create table #d --detail
(
oid char(4) not null,
lid int not null,
prc money not null,
ship char(5) not null,
sdt date null
)
insert #m values ('ord1','cst1','1/1/11')
insert #m values ('ord2','cst2','2/2/11')
insert #m values ('ord3','cst2','3/2/11')
insert #m values ('ord4','cst3','4/3/11')
insert #m values ('ord5','cst1','5/1/11')
insert #d values ('ord1',1,110.00,'usps','1/11/11')
insert #d values ('ord1',2,120.00,'usps','1/12/11')
insert #d values ('ord1',3,130.00,'usps',null)
insert #d values ('ord2',1,200.00,'fedex',null)
insert #d values ('ord2',2,210.00,'fedex','2/2/2')
insert #d values ('ord3',1,300.00,'ups','3/3/3')
insert #d values ('ord4',1,400.00,'dhl',null)
insert #d values ('ord4',2,410.00,'dhl','4/1/04')
insert #d values ('ord5',1,500.00,'rapid',null)
--the data query
--select m.oid, m.cid, m.odt, d.lid, d.prc, d.ship, d.sdt
--from #m as m
--join #d as d on m.oid = d.oid
select m.oid, m.cid, m.odt, d1.lid as [line1], d1.ship, d1.sdt, d2.lid as [line2], d2.ship, d2.sdt, d3.lid as [line3], d3.ship, d3.sdt
from #m as m
full join #d as d1 on M.oid = d1.oid and d1.lid = 1
full join #d as d2 on M.oid = d2.oid and d2.lid = 2
full join #d as d3 on M.oid = d3.oid and d3.lid = 3
where m.oid is not null
drop table #m
drop table #d
go
February 22, 2012 at 5:45 pm
This is a good example but would this be restricted to the number of d.lid's (in this case hard-coded to only 3 line items?)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 22, 2012 at 5:57 pm
You can hard code as many joins as you like up to I think some maximum.
Practically speaking, depending on the number of fields displayed per line item, you'll run out of paper if you insist on one order per line ;-). You can however create multiple views: a for line items 0-9, b for 10-19, etc.
February 23, 2012 at 6:53 am
Hi,
Based on Jessie's good work, try the following SQL.
Put it before
drop #m
drop #d
Select T.oid, T.cid, T.odt, Max(T.step1) AS TStep1, Max(T.Step2) AS TStep2, Max(T.Step3) AS TStep3
FROM (
Select M.*
, CASE WHEN D.lid = 1 THEN 'Step1' ELSE Null END AS Step1
, CASE WHEN D.lid = 2 THEN 'Step2' ELSE Null END AS Step2
, CASE WHEN D.lid = 3 THEN 'Step3' ELSE Null END AS Step3
From #m AS M INNER JOIN #d AS D ON M.oid = D.oid
) AS T
GROUP BY oid, cid, odt
February 23, 2012 at 11:35 am
This is by far the most whacked out code I've ever written and would never actually use it for anything other than the fun of "trying it", but I believe does what you wanted it to do:create table #header (hidx int identity(1,1), hordno int, custname varchar(10), orddate datetime)
create table #detail (didx int identity(1,1), dordno int, orditem int, price money, method varchar(10))
create table #items (iidx int identity(1,1), iordno int, items int)
create table #final (ordno int, custname varchar(10), orddate datetime,
orditem1 int null, price1 money null, method1 varchar(10) null,
orditem2 int null, price2 money null, method2 varchar(10) null,
orditem3 int null, price3 money null, method3 varchar(10) null)
/* You would add as many items as you could have in your table :: orditem4, price4,...etc */
insert into #header select * from orderheader
insert into #detail select * from orderdetail
insert into #items select hordno, count(orditem) from #header JOIN #detail ON hordno = dordno group by hordno
declare @hcount int, @cnt1 int = 1, @cnt2 int = 1, @hordno int, @orditem int, @ocnt int
set @hcount = (select max(hidx) from #header)
declare @SQLBegin varchar(2500), @sql varchar(2500), @sqlend varchar(2500)
set @sql = ''
while @cnt1 <= @hcount
begin
set @hordno = (select hordno from #header where hidx = @cnt1)
set @sqlend = 'from #header where hordno = ' + cast(@hordno as varchar(6))
set @ocnt = (select items from #items where iordno = @hordno)
/* conditions needed for as many items in the temp table, so if there were 4 possible items, you'd need 4 IF's */
IF (@ocnt = 1)
SET @SQLBegin = 'insert into #final (ordno, custname, orddate, orditem1, price1, method1)
select hordno, custname, orddate'
IF (@ocnt = 2)
SET @SQLBegin = 'insert into #final (ordno, custname, orddate, orditem1, price1, method1, orditem2, price2, method2)
select hordno, custname, orddate'
IF (@ocnt = 3)
SET @SQLBegin = 'insert into #final select hordno, custname, orddate'
While @cnt2 < @ocnt +1
begin
set @sql = @sql + '(select orditem from #detail where dordno = ' + cast(@hordno as varchar(6)) + ' and orditem = '
+ cast(@cnt2 as varchar(6)) + ') [Item' + cast(@cnt2 as varchar(6)) + '],
(select price from #detail where dordno = ' + cast(@hordno as varchar(6)) + ' and orditem = '
+ cast(@cnt2 as varchar(6)) + ') [Price' + cast(@cnt2 as varchar(6)) + '],
(select method from #detail where dordno = ' + cast(@hordno as varchar(6)) + ' and orditem = '
+ cast(@cnt2 as varchar(6)) + ') [Method' + cast(@cnt2 as varchar(6))
+ case when (@cnt2 = @ocnt +1) then '] ' ELSE '],' END
set @cnt2 = @cnt2 + 1
end
set @sql = (@sqlbegin + ', ' + @sql + ' ' + @sqlend)
set @sql = REPLACE(@sql, '], fr', '] fr')
exec (@sql)
set @sql = ''
set @cnt1 = @cnt1 + 1
SET @cnt2 = 1
end
select * from #final
drop table #header
drop table #detail
drop table #items
drop table #final
Output - see attached image
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply