Show data from 2 differentt rows in same row

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

Show data from 2 differentt rows in same row

Author

Topic

javahf
Starting Member

34 Posts

Posted - 2011-04-04 : 19:03:03

We have inquiry from 2 different tables Orders and orderDetails - which are linked by orderid - some info comes from Orders and some from orderdetails - But in orderdetails we may have several rows with the same orderid - we like to show the field ProductName from these records on the same row for the same orderid . The inquiry below repeats the same order on several line .
Can you help

SELECT Orders.CustomerID ,
Orders.OrderID,
OrderDetails.ProductName ,
Orders.OrderDate, Orders.OrderStatus, Orders.BillingFirstName,
Orders.BillingLastName ,Orders.ShipState, Orders.BillingCity,
Orders.BillingState, Orders.BillingCountry, OrderDetails.ProductCode,Orders.PaymentAmount,Orders.TotalShippingCost, Orders.SalesTax1 ,(Orders.PaymentAmount - Orders.TotalShippingCost - Orders.SalesTax1) AS [Net Order Amt]
FROM
Orders , OrderDetails

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 19:11:57

I think you are almost there. At the end of what you now have, add

Where Orders.orderID = OrderDetails.OrderId

If that is not what you were looking for, post some sample data that you like to see.

javahf
Starting Member

34 Posts

Posted - 2011-04-04 : 19:25:01

data is like this
Order ( has one record per order )
Order number , name , zip code etc so for example
1 , John , 85201,
2,mike , 85999

Order detail has several records per order so
order number ,product description , price , tax
1 , bicycle , 100
1,tire , 10
2,socket,40
2,paint,30
we get :
1,john , 85201 .bicycle,100
1,john,85201,tire,10
2,mike,85999,socket,40
2,mike,8599,paint.30
we like to get
1,john,85201,bicycle,tire,110( this total amount field is available on order )
2,mike,85999,socket,paint ,70

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 19:40:53

You will need to use dynamic pivoting to do this. Madhivanan's blog here http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx has details, and even an example very much like your problem.

If every order had only two products each, the problem would be simpler. But I cant imagine that every order would have only two products each.

javahf
Starting Member

34 Posts

Posted - 2011-04-04 : 19:46:00

It looks very complicated and I am not following it as I am very novice to MS SQL

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 19:56:47

I am typing this on a mobile device, not the easiest thing to do SQL programming with. Are you looking for a comma separated list of products, or each product in its own column? If in its own column, the resulting tamle would be jagged - not the kind of thing SQL likes very much.

javahf
Starting Member

34 Posts

Posted - 2011-04-04 : 20:47:23

this routine can create a csv file

javahf
Starting Member

34 Posts

Posted - 2011-04-04 : 20:57:14

Here is the full report now
SELECT Orders.CustomerID ,
Orders.OrderID,
OrderDetails.ProductName ,
Orders.OrderDate, Orders.OrderStatus, Orders.BillingFirstName,
Orders.BillingLastName ,Orders.ShipState, Orders.BillingCity,
Orders.BillingState, Orders.BillingCountry, OrderDetails.ProductCode,Orders.PaymentAmount,Orders.TotalShippingCost, Orders.SalesTax1 ,(Orders.PaymentAmount - Orders.TotalShippingCost - Orders.SalesTax1) AS [Net Order Amt]
FROM
Orders , OrderDetails
where ( Orders.OrderID > 1179 and
Orders.OrderID = OrderDetails.OrderID )
ORDER BY
Orders.OrderID

javahf
Starting Member

34 Posts

Posted - 2011-04-04 : 23:45:57

anyone can help ? and create the report the way I explained ?

McDebil
Starting Member

23 Posts

Posted - 2011-04-05 : 06:32:20

HI,

first create 2 functions based on the following for the produt names and the product codes:

CREATE FUNCTION jfn_getProductNames
(
@OrderID INTEGER,
@Separator varchar(2)
)
RETURNS nvarchar(max)
AS BEGIN
DECLARE @ResultList nvarchar(max)
DECLARE @Table table
(
[id] [int] IDENTITY(1, 1)
NOT NULL,
[ProductName] NVARCHAR(255) NOT NULL
)

DECLARE @Count INTEGER
DECLARE @Counter INTEGER

SET @Counter = 1
SET @ResultList = ''
INSERT @Table ( ProductName )
SELECT ProductName
FROM OrderDetails
WHERE OrderID = @OrderID

SELECT @Count = MAX([id]) + 1
FROM @Table

WHILE @Counter < @Count
BEGIN

SELECT @ResultList = @ResultList + ProductName + @Separator
FROM @Table
WHERE [id] = @Counter
SET @Counter = @Counter + 1

END

SET @ResultList = SUBSTRING(@ResultList, 1, LEN(@ResultList) - 1)

RETURN @ResultList

END

and use the functions which is put separated list into one field in the report:

SELECT DISTINCT
Orders.CustomerID ,
Orders.OrderID,
dbo.jfn_getProductNames (Orders.OrderID, ', ') AS ProductNames ,
Orders.OrderDate,
Orders.OrderStatus,
Orders.BillingFirstName,
Orders.BillingLastName ,
Orders.ShipState,
Orders.BillingCity,
Orders.BillingState,
Orders.BillingCountry,
dbo.jfn_getProductCodes (Orders.OrderID, ', ') AS ProductCodes,
Orders.PaymentAmount,
Orders.TotalShippingCost,
Orders.SalesTax1 ,
(Orders.PaymentAmount - Orders.TotalShippingCost - Orders.SalesTax1) AS [Net Order Amt]
FROM
Orders

McDebil

matty
Posting Yak Master

161 Posts

Posted - 2011-04-05 : 07:08:54

SELECT o.Ordernumber,o.name,o.zipcode,
STUFF((SELECT ',' + d.productdescription FROM Orderdetail d WHERE o.Ordernumber = d.ordernumber FOR XML PATH('')),1,1,'') AS productdescription
FROM Order o

javahf
Starting Member

34 Posts

Posted - 2011-04-05 : 23:50:01

quote:
Originally posted by McDebil

HI,

first create 2 functions based on the following for the produt names and the product codes:

CREATE FUNCTION jfn_getProductNames
(
@OrderID INTEGER,
@Separator varchar(2)
)
RETURNS nvarchar(max)
AS BEGIN
DECLARE @ResultList nvarchar(max)
DECLARE @Table table
(
[id] [int] IDENTITY(1, 1)
NOT NULL,
[ProductName] NVARCHAR(255) NOT NULL
)

DECLARE @Count INTEGER
DECLARE @Counter INTEGER

SET @Counter = 1
SET @ResultList = ''
INSERT @Table ( ProductName )
SELECT ProductName
FROM OrderDetails
WHERE OrderID = @OrderID

SELECT @Count = MAX([id]) + 1
FROM @Table

WHILE @Counter < @Count
BEGIN

SELECT @ResultList = @ResultList + ProductName + @Separator
FROM @Table
WHERE [id] = @Counter
SET @Counter = @Counter + 1

END

SET @ResultList = SUBSTRING(@ResultList, 1, LEN(@ResultList) - 1)

RETURN @ResultList

END

and use the functions which is put separated list into one field in the report:

SELECT DISTINCT
Orders.CustomerID ,
Orders.OrderID,
dbo.jfn_getProductNames (Orders.OrderID, ', ') AS ProductNames ,
Orders.OrderDate,
Orders.OrderStatus,
Orders.BillingFirstName,
Orders.BillingLastName ,
Orders.ShipState,
Orders.BillingCity,
Orders.BillingState,
Orders.BillingCountry,
dbo.jfn_getProductCodes (Orders.OrderID, ', ') AS ProductCodes,
Orders.PaymentAmount,
Orders.TotalShippingCost,
Orders.SalesTax1 ,
(Orders.PaymentAmount - Orders.TotalShippingCost - Orders.SalesTax1) AS [Net Order Amt]
FROM
Orders

McDebil

Thank you - I tried this and I get message
Your SQL is invalid: Incorrect syntax near the keyword 'SELECT'

McDebil
Starting Member

23 Posts

Posted - 2011-04-06 : 00:25:11

Which SELECT is invalid?

The functions working properly?

I tested the product name function only.

McDebil

javahf
Starting Member

34 Posts

Posted - 2011-04-06 : 00:37:09

quote:
Originally posted by matty

SELECT o.Ordernumber,o.name,o.zipcode,
STUFF((SELECT ',' + d.productdescription FROM Orderdetail d WHERE o.Ordernumber = d.ordernumber FOR XML PATH('')),1,1,'') AS productdescription
FROM Order o

Thank you very much - this works - I put the real data in there which looks like
SELECT o.Orderid,o.BillingLastName,o.BillingState,o.PaymentAmount,o.TotalShippingCost, o.SalesTax1 ,
STUFF((SELECT ',' + d.OptionID FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') AS
OptionID
FROM Orders o

The field d.OptionID is "LONG" field so I get an error
Your SQL is invalid: Conversion failed when converting the varchar value ',' to data type int.
Can you assist and correcting the string

javahf
Starting Member

34 Posts

Posted - 2011-04-06 : 00:56:54

That is all the message I get with this software package !

McDebil
Starting Member

23 Posts

Posted - 2011-04-06 : 00:57:49

Use CAST or CONVERT on OptionID field:

SELECT o.Orderid,o.BillingLastName,o.BillingState,o.PaymentAmount,o.TotalShippingCost, o.SalesTax1 ,
STUFF((SELECT ',' + CAST(d.OptionID AS VARCHAR(50)) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') AS
OptionID
FROM Orders o

Read more the Books online!

McDebil

javahf
Starting Member

34 Posts

Posted - 2011-04-06 : 00:58:26

quote:
Originally posted by McDebil

Which SELECT is invalid?

The functions working properly?

I tested the product name function only.

That is all the message I get with this software package !

McDebil

McDebil
Starting Member

23 Posts

Posted - 2011-04-06 : 01:00:28

RE: Posted - 04/06/2011 : 00:56:54

Try run the SQL code in the management studio and try create trace on the database.

McDebil

javahf
Starting Member

34 Posts

Posted - 2011-04-06 : 01:03:02

quote:
Originally posted by McDebil

Use CAST or CONVERT on OptionID field:

SELECT o.Orderid,o.BillingLastName,o.BillingState,o.PaymentAmount,o.TotalShippingCost, o.SalesTax1 ,
STUFF((SELECT ',' + CAST(d.OptionID AS VARCHAR(50)) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') AS
OptionID
FROM Orders o

Read more the Books online!

McDebil

Thanks - I will !
I tried this and now I get
Your SQL is invalid: Incorrect syntax near '&'

McDebil
Starting Member

23 Posts

Posted - 2011-04-06 : 01:14:32

Contact to the software vendor support: The error message is invalid. The SQL SERLECT code don't contain '&' character.

Try run the command in the Manadement studio!!!

McDebil

javahf
Starting Member

34 Posts

Posted - 2011-04-06 : 02:19:04

quote:
Originally posted by McDebil

Contact to the software vendor support: The error message is invalid. The SQL SERLECT code don't contain '&' character.

Try run the command in the Manadement studio!!!

McDebil

Thanks everyone - it is working now ! you have save us a lot of time and effort . Really appreciate all your help McDebil and matty

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources