Query help, two rows(?) in one line

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

Query help, two rows(?) in one line

Author

Topic

CVDK
Starting Member

4 Posts

Posted - 2012-10-31 : 06:39:50

I have a query like this which I'd like to add some information to. The query generates a specification of an invoice. Each line specifies that we move something from point A but lacks the information of the destination, point B. Information on point A and B is in the table orderstops as respectively row 1 and row 2, from those two rows I'd like to exctract the field orderstops.place and make my query return them in the same line (either in seperate columns or joined in the same column). If I edit the line "and os.suborderno=1" to "and os.suborderno in (1, 2) both rows are returned but as two seperate lines in the same column

I've unsuccesfully tried to figure out various solutions myself. As my SQL skills are not exactly world class I hope that someone here could help me out?

SELECT
ivd.orderid, CONVERT(varchar(8),ivd.linedate,5) linedate, ivd.waittime, ivd.kilometer,
ivd.price, ivd.outlay, ivd.vat, st.servicename, CONVERT(varchar(255),ivd.description),
CONVERT(varchar(255),os.place) 'From', CONVERT(varchar(255), ivd.custref) custref,
ivd.department, ivm.customerid, cu.custcall, ivm.invoiceno, dr.initial

FROM
invoicedetail ivd with (nolock), invoicemain ivm with (nolock), servicetype st with (nolock),
customer cu with (nolock), orderstops os with (nolock), driver dr with (nolock)

WHERE
ivd.invoiceid=ivm.invoiceid
and os.suborderno=1
and os.driverid*=dr.driverid
and ivd.orderid=os.orderno
and ivd.servicetypeid=st.servicetypeid
and ivm.customerid=cu.custno
and ivm.invoiceno in (@invoice)
and os.readydate between st.fromdate and st.todate

ORDER BY
ivd.orderid

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-31 : 06:52:23

Show us the results of your query....

Run once this code.. You may get idea

CREATE TABLE TestTable (ID INT, Col VARCHAR(4))
GO
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E'
GO
SELECT *
FROM TestTable
GO

-- Get CSV values
SELECT t.ID, STUFF((SELECT ',' + s.Col FROM TestTable s WHERE s.ID = t.ID FOR XML PATH('')),1,1,'') AS CSV
FROM TestTable AS t
GROUP BY t.ID
GO

DROP TABLE TestTable

--
Chandu

CVDK
Starting Member

4 Posts

Posted - 2012-10-31 : 07:02:40

Sorry, Chandu
I did not understand the code but I take it that you mean I should use Union in some way?

Here's part of my result:

orderid linedate waittime kilometer price outlay vat servicename Description From custref department customerid custcall invoiceno initial
6002552 03-09-12 0 0 999 0 TRUE EKSPRES XXXX Company, Address->Company, Address 9A INIT-1000-1234 1 1234567 XXXXXX 111111 543
6013183 03-09-12 0 0 999 0 TRUE EKSPRES XXXX Company, Address->Company, Address EG INIT-1000-1234 1 1234567 XXXXXX 111111 966
6015482 03-09-12 0 0 999 0 TRUE EKSPRES XXXX Company, Address->Company, Address 6A INIT-1000-1234 1 1234567 XXXXXX 111111 960
6016188 03-09-12 0 0 999 0 TRUE EKSPRES XXXX Company, Address->Company, Address 9FS.39 INIT-1000-1234 1 1234567 XXXXXX 111111 952

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-31 : 07:16:32

[code]SELECT
ivd.orderid,
CONVERT(varchar(8),ivd.linedate,5) linedate,
ivd.waittime,
ivd.kilometer,
ivd.price,
ivd.outlay,
ivd.vat,
st.servicename,
CONVERT(varchar(255),ivd.description),

CONVERT(varchar(255),osfrom.place) 'From',
CONVERT(varchar(255),osto.place) 'To',

CONVERT(varchar(255), ivd.custref) custref,
ivd.department,
ivm.customerid,
cu.custcall,
ivm.invoiceno,
dr.initial

FROM
invoicedetail ivd
join invoicemain ivm on ivd.invoiceid=ivm.invoiceid and ivm.invoiceno = @invoice
join servicetype st on ivd.servicetypeid=st.servicetypeid
join customer cu on ivm.customerid=cu.custno
join orderstops osfrom on ivd.orderid=osfrom.orderno and osfrom.suborderno=1 and osfrom.readydate between st.fromdate and st.todate
join orderstops osto on ivd.orderid=osto.orderno and osto.suborderno=2 and osto.readydate between st.fromdate and st.todate
left join driver dr on osfrom.driverid = dr.driverid
[/code]

Too old to Rock'n'Roll too young to die.

CVDK
Starting Member

4 Posts

Posted - 2012-10-31 : 07:23:36

quote:

Originally posted by webfred

SELECT 
ivd.orderid, 
CONVERT(varchar(8),ivd.linedate,5) linedate, 
ivd.waittime, 
ivd.kilometer, 
ivd.price, 
ivd.outlay, 
ivd.vat, 
st.servicename, 
CONVERT(varchar(255),ivd.description), 

CONVERT(varchar(255),osfrom.place) 'From', 
CONVERT(varchar(255),osto.place) 'To', 

CONVERT(varchar(255), ivd.custref) custref, 
ivd.department, 
ivm.customerid, 
cu.custcall, 
ivm.invoiceno, 
dr.initial


FROM 
invoicedetail ivd 
join invoicemain ivm on ivd.invoiceid=ivm.invoiceid and ivm.invoiceno = @invoice
join servicetype st on ivd.servicetypeid=st.servicetypeid
join customer cu on ivm.customerid=cu.custno
join orderstops osfrom on ivd.orderid=osfrom.orderno and osfrom.suborderno=1 and osfrom.readydate between st.fromdate and st.todate
join orderstops osto on ivd.orderid=osto.orderno and osto.suborderno=2 and osto.readydate between st.fromdate and st.todate
left join driver dr on osfrom.driverid = dr.driverid

Too old to Rock'n'Roll too young to die.

Thank you for your input Webfred but your code gives me a result something like this (only initials are returned and way to many lines)

orderid linedate waittime kilometer price outlay vat servicename Descpription From To custref department customerid custcall invoiceno initial
501
553
564
523
566

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-31 : 07:35:06

maybe this

SELECT 
ivd.orderid, CONVERT(varchar(8),ivd.linedate,5) linedate, ivd.waittime, ivd.kilometer, 
ivd.price, ivd.outlay, ivd.vat, st.servicename, CONVERT(varchar(255),ivd.description), 
CONVERT(varchar(255),os.place) 'From', CONVERT(varchar(255),osto.place) 'To', CONVERT(varchar(255), ivd.custref) custref, 
ivd.department, ivm.customerid, cu.custcall, ivm.invoiceno, dr.initial


FROM 
invoicedetail ivd with (nolock), invoicemain ivm with (nolock), servicetype st with (nolock), 
customer cu with (nolock), orderstops os with (nolock), orderstops osto with (nolock), driver dr with (nolock)

WHERE 
ivd.invoiceid=ivm.invoiceid
and os.suborderno=1 
and osto.suborderno=2
and os.driverid*=dr.driverid
and osto.driverid*=dr.driverid
and ivd.orderid=os.orderno
and ivd.orderid=osto.orderno
and ivd.servicetypeid=st.servicetypeid
and ivm.customerid=cu.custno
and ivm.invoiceno in (@invoice)
and os.readydate between st.fromdate and st.todate
and osto.readydate between st.fromdate and st.todate

Too old to Rock'n'Roll too young to die.

CVDK
Starting Member

4 Posts

Posted - 2012-10-31 : 07:59:08

quote:

Originally posted by webfred

maybe this

SELECT 
ivd.orderid, CONVERT(varchar(8),ivd.linedate,5) linedate, ivd.waittime, ivd.kilometer, 
ivd.price, ivd.outlay, ivd.vat, st.servicename, CONVERT(varchar(255),ivd.description), 
CONVERT(varchar(255),os.place) 'From', CONVERT(varchar(255),osto.place) 'To', CONVERT(varchar(255), ivd.custref) custref, 
ivd.department, ivm.customerid, cu.custcall, ivm.invoiceno, dr.initial


FROM 
invoicedetail ivd with (nolock), invoicemain ivm with (nolock), servicetype st with (nolock), 
customer cu with (nolock), orderstops os with (nolock), orderstops osto with (nolock), driver dr with (nolock)

WHERE 
ivd.invoiceid=ivm.invoiceid
and os.suborderno=1 
and osto.suborderno=2
and os.driverid*=dr.driverid
and osto.driverid*=dr.driverid
and ivd.orderid=os.orderno
and ivd.orderid=osto.orderno
and ivd.servicetypeid=st.servicetypeid
and ivm.customerid=cu.custno
and ivm.invoiceno in (@invoice)
and os.readydate between st.fromdate and st.todate
and osto.readydate between st.fromdate and st.todate

Too old to Rock'n'Roll too young to die.

That was amazing!

Thanks a million Webfred

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources