query - 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

query

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-04-02 : 12:15:55

orderid product quantity
100 A 1
100 B 2
101 A 2
100 A 3
101 C 4
102 A 1
102 D 5

product table has
A
B
C
D

the resultant table should sum the quantities for each product (for example, orderid 100
has two rows with product A, so it has to be summed up(1+3=4)).

the number of products in the products table can be increased later on, so the columns has to
fit the future purpose. so it has to be variable columns

since the products table has 4 rows, the output table has 4 columns.
if later, the number of products increased, let's say
A
B
C
D
E
F
G
then there should be 7 columns to fit the quantity. based on the count of products in the products table, the columns will be increased. and if the quantities corresponding to that product exists, then it will show with the quantity, else will remain blank

output table:
100 4 2
101 2 4
102 1 5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-02 : 13:57:53

[code]
SELECT n.orderid,
SUM(CASE WHEN product='A' THEN quantity END) AS [A],
SUM(CASE WHEN product='B' THEN quantity END) AS [B],
...
FROM
(
SELECT orderid,product
FROM (SELECT DISTINCT Orderid FROM table1) t
cross join products p
)m
LEFT JOIN table n
ON n.orderid=m.orderid
and n.product = m.product
GROUP BY n.orderid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gagani
Posting Yak Master

112 Posts

Posted - 2012-04-02 : 14:58:11

orderid product quantity trantimestamp
100 A 1 01/01/2001
100 B 2 02/02/2001
101 A 2 01/03/2001
100 A 3 01/02/2001
101 C 4 03/02/2001
102 A 1 04/02/2001
102 D 5 04/04/2001

Thank you for your reply. In fact, I want to use the query in the stored procedure with the datetime column as the input parameters. For the increased number of rows in the products table later on, the stored procedure needs to be changed too. Is there anyway, we can dynamically get the columns based on the increased number of rows in the products table(which would happen in the future) without changing the stored procedure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-02 : 15:44:41

you can use logic below

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-04 : 04:14:28

SELECT Distinct OrderId, Product, SUM(Quantity) OVER(PARTITION BY OrderID, Product ) As Total FROM Orders

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-04 : 18:20:15

OP is looking at cross tabbed results as explained below

since the products table has 4 rows, the output table has 4 columns.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gagani
Posting Yak Master

112 Posts

Posted - 2012-04-05 : 06:13:02

for the given table,

table_3
orderid product quantity
100 A 1
100 B 1
101 A 1
100 C 1
100 D 1

for the output:

orderid A B C D
100 1 1 1 1
101 1

I have written the stored procedure in 2008 to dynamically fit, but is not working in 2000. can anyone help

CREATE TABLE #REPORT(ORDERID VARCHAR(30))
Insert into #REPORT SELECT DISTINCT ORDERID FROM TABLE_3
DECLARE @OP_TABLE TABLE (OrderId bigint)
DECLARE @ALL_ORDERS AS VARCHAR(50)
DECLARE @ORDERID AS BIGINT
DECLARE @INSRTSTATEMENT AS VARCHAR(2000)
DECLARE @NEW_COLUMN AS VARCHAR(200)

DECLARE CUR1 CURSOR FOR SELECT DISTINCT PRODUCT_ID FROM TABLE_3 where
PRODUCT_ID<>''
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @ALL_ORDERS

WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @NEW_COLUMN = @ALL_ORDERS --+ CONVERT(VARCHAR(100),@CNT)
SET @INSRTSTATEMENT = 'ALTER TABLE #REPORT ADD [' + @NEW_COLUMN + '] varchar(50)'
EXEC(@INSRTSTATEMENT)

SET @INSRTSTATEMENT = 'UPDATE A SET [' + @NEW_COLUMN + '] = (QUANTITY) FROM #REPORT A, TABLE_3 B WHERE A.ORDERID=B.ORDERID AND PRODUCT_ID=''' + @ALL_ORDERS + ''''

EXEC(@INSRTSTATEMENT)
FETCH NEXT FROM CUR1 INTO @ALL_ORDERS
END
SELECT * FROM #REPORT
CLOSE CUR1
DEALLOCATE CUR1
END

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-05 : 07:43:25

Instead of the long stored procedure, you could use pivot table as follows:

SELECT OrderId, A, B, C, D
FROM 
(
SELECT OrderId, Product, Quantity
FROM Ex AS F
) AS SourceTable
PIVOT
(
SUM(Quantity)
FOR Product IN (A, B, C, D)
) AS PivotTable

Hope that helps you

gagani
Posting Yak Master

112 Posts

Posted - 2012-04-05 : 07:51:28

the rows may be increased later on with different products.
I am trying to fit dynamically to fit for the future purpose, i do
not want to restrict my query to A,B,C,D itself. No matter how many increase of products for any orderid with let's say E, F, G....
I do not want to change the stored procedure later on for any increase of products later.
For that I have written in that way, but the problem is, it's not working
in 2000. it's fine in 2008

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-05 : 08:46:29

I tried using a simpler Dynamic SQL to go with my code.....I think this addresses your any no. of products issue.


DECLARE @cols NVARCHAR(2000)
Declare @query NVARCHAR(4000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t2.point_id
                        FROM    #table1 AS t2
                        ORDER BY '],[' + t2.point_id
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'
SET @query = N'SELECT [ID], '+
@cols +'
FROM 
(
SELECT [ID], [Point_ID], Point
FROM #table1 AS F
) AS SourceTable
PIVOT
(
SUM(POINT)
FOR [POINT_ID] IN ([P001], [P002], [P003],[P004],[P005])
) AS PivotTable'

Execute (@query)

Try running the above query on your sample data and by adding some additional data into your table.

Hope this helped you,
Vinu Vijayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 10:37:21

thats what posted link does

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-06 : 00:24:32

I agree with u vaisakh. The simple pivot table example i gave at first was before i saw ur post carefully. I gave the solution based on just the sample data and didn't think further. Sorry!!...that was my bad. But, when i understood that he was interested in the dynamic pivot table that is when I made the last post.
Was just trying to make things a little easy. Since I had misunderstood him in the last post.

PS: I have read a lot of your solutions Vaisakh. I must tell you that you're really good bro. I'm a fan

Btw I'm a malayali too.

Vinu Vijayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 00:36:04

quote:
Originally posted by vinu.vijayan

I agree with u vaisakh. The simple pivot table example i gave at first was before i saw ur post carefully. I gave the solution based on just the sample data and didn't think further. Sorry!!...that was my bad. But, when i understood that he was interested in the dynamic pivot table that is when I made the last post.
Was just trying to make things a little easy. Since I had misunderstood him in the last post.

PS: I have read a lot of your solutions Vaisakh. I must tell you that you're really good bro. I'm a fan Btw I'm a malayali too.

Vinu Vijayan

No problem
NOM
I was just telling OP that link explains a way of doing it

Also I already interpreted it from your name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources