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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 PIVOT

Author  Topic 

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-01-28 : 08:07:18
hi

i have this table

orderNo ProductKey Amount
O1 P1 100
O2 P2 200
O1 P3 300


i want to get this table:

ProductKey O1 O2
P1 100
P2 200
P3 300

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-01-28 : 09:22:22
[code]
;WITH aCTE
AS
(SELECT 'O1' AS orderNo , 'P1' AS ProductKey , 100 AS Amount
UNION ALL SELECT 'O2','P2', 200
UNION ALL SELECT 'O1','P3', 300 )


SELECT
*
FROM
aCTE
PIVOT(SUM(AMOUNT) FOR orderNo IN ([O1],[O2]))A

[/code]


sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-01-28 : 09:22:39
output:

ProductKey O1 O2
P1 100 NULL
P2 NULL 200
P3 300 NULL



sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-01-28 : 09:25:06
And if you that NULL value should be replace by empty space , then use:


SELECT
ProductKey
,ISNULL(CAST([O1] AS VARCHAR(30)),'') AS [O1]
,ISNULL(CAST([O2] AS VARCHAR(30)),'') AS [O2]
FROM
aCTE
PIVOT(SUM(AMOUNT) FOR orderNo IN ([O1],[O2]))A





sabinWeb MCP
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-01-28 : 14:15:23
but, i do not know what value i have in orderNo
O1,O2,O3 it is just example


thanks
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-28 : 14:39:57
In that case, you'll need to use Dynamic SQL to build your pivot. But, just how many order numbers are we talking about? What's the maximum?
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-01-28 : 15:04:05
1.how do you build dynamic SQL?
2.Six orders is a maximum
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-28 : 15:44:57
Do you mean, "There can be no more than six orders per product" or "I only want to see up to six orders per product"
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-01-29 : 00:00:04
There can be no more than six orders per product

thanks
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-29 : 07:12:23
in that case just expand the pivot example you were given to include the 6 possible order IDs.
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-02-01 : 07:57:43
but i do not know the name of the orders
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-01 : 13:09:03
So how many distinct order IDs can there B
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-02-01 : 14:08:05
6
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-01 : 22:21:57
Only 6? Would you pleas list the 6 distinct order numbers?
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-02-02 : 00:52:02
As i write before ,the 6 orders Number all the time change
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-02-02 : 01:06:33
See if this helps http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-02 : 07:02:43
it doesn't matter if the order numbers change all the time. Since there are only 6 of them possible to what you said, simply list them all in the pivot clause.
Go to Top of Page
   

- Advertisement -