Microsoft SQL Server 2008
Microsoft SQL Server 2008

SQL Query of this Tables

Attached one Excel file, which i stored one table records

I want to output of record which i defined in excel file.

Please check excel file and give me proper solutions.

Thank you.
Tablerecords.xls

citadelindAsked: 2011-03-09

Who is Participating?

Solutions

LVL 41

SharathConnect With a Mentor Data EngineerCommented: 2011-03-10

try this.

SELECT DISTINCT Order#, 
                OrderDate, 
                PaymentType, 
                SUM(Price) 
                  OVER(PARTITION BY Order# )                Price, 
                RTRIM(SUBSTRING(ISNULL((SELECT ',' + ProductName 
                                          FROM your_table t2 
                                         WHERE t1.Order# = t2.Order# 
                                        for xml path('')),' '),2,2000)) ProductName, 
                SUM([Weight]) 
                  OVER(PARTITION BY Order# )             [Weight], 
                ShipType, 
                ShipVia, 
                Company, 
                FirstName, 
                LastName, 
                [Address], 
                City, 
                [State], 
                Zip, 
                Country, 
                Phone, 
                Email, 
                Comments 
  FROM your_table t1

Open in new window

LVL 5

sameer_goyalCommented: 2011-03-09

The information looks incomplete. I assume you want to fetch all records grouped on a particular customer?

Is that correct? If not, then let me know your filter creiterions and i will provide you the Sql query..

citadelindAuthor Commented: 2011-03-09

Yes all records come form different tables like products,orders and customers.
So i am using with join query all three tables and make this output

but in product name, weight and price column are different and remaining are same records
so i want to put in one rows so i get output.

Please provide me query on this problem.

Thank you.

LVL 41

SharathData EngineerCommented: 2011-03-10

Try this.

SELECT DISTINCT Order#, 
                OrderDate, 
                PaymentType, 
                Price, 
                RTRIM(SUBSTRING(ISNULL((SELECT ',' + ProductName 
                                          FROM your_table t2 
                                         WHERE t1.Order# = t2.Order# 
                                        for xml path('')),' '),2,2000)) ProductName, 
                SUM([Weight]) 
                  OVER(PARTITION BY Order# )             [Weight], 
                ShipType, 
                ShipVia, 
                Company, 
                FirstName, 
                LastName, 
                [Address], 
                City, 
                [State], 
                Zip, 
                Country, 
                Phone, 
                Email, 
                Comments 
  FROM your_table t1

Open in new window

citadelindAuthor Commented: 2011-03-10

Thank you for giving good query solution

But this query do not DISTINCT of the record. see the attach excel file which giving me output of result

It displays 2 record same.

Please give me solution.

Output.xls

citadelindAuthor Commented: 2011-03-10

Please give me solution of about query

But this query do not DISTINCT of the record. see the attach excel file which giving me output of result

It displays 2 record same.

Please give me solution.

Output.xls