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

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

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

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

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

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?

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

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"

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-01-29 : 00:00:04

There can be no more than six orders per product

thanks

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.

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-02-01 : 07:57:43

but i do not know the name of the orders

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-01 : 13:09:03

So how many distinct order IDs can there B

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-02-01 : 14:08:05

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?

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

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources