[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Piviot table? Move columns to rows

I'm drawing a blank and know this is simple. How can I take the following:

Item                   Proform                  ProTypeA
aaaa                     10,000                     100,000
bbbb                     205                            50,000
cccc                      19333                           29,000

and switch cols to rows like

Type              Item                  Qty
Proform         aaaa                   10,000
ProTypeA       aaaa                    100,000
Proform         bbbb                   205
ProTypeA       bbbb                    50,000

and so forth. thanks in advance
0
MadIce
Asked:
MadIce
  • 2
1 Solution
 
VIVEKANANDHAN_PERIASAMYCommented:
0
 
Anthony PerkinsCommented:
This is how you do it:
SELECT	[Type],
	 Item, Qty
FROM  (
        SELECT  Item,
		Proform,
		ProTypeA
        FROM    YourTable
            ) p
UNPIVOT	(Qty FOR [Type] IN (Proform, ProTypeA) )u

Open in new window

0
 
Anthony PerkinsCommented:
This is how I tested it:
DECLARE @YourTable TABLE (Item char(4), Proform integer, ProTypeA INTEGER)

INSERT @YourTable(Item, Proform, ProTypeA)
VALUES  ('aaaa', 10000, 100000),
	('bbbb',  205, 50000),
	('cccc', 19333, 29000)

SELECT	[Type],
	 Item, Qty
FROM  (
        SELECT  Item,
		Proform,
		ProTypeA
        FROM    @YourTable
            ) p
UNPIVOT	(Qty FOR [Type] IN (Proform, ProTypeA) )u

Output:
Type		Item	Qty
Proform		aaaa	10000
ProTypeA	aaaa	100000
Proform		bbbb	205
ProTypeA	bbbb	50000
Proform		cccc	19333
ProTypeA	cccc	29000

Open in new window

0
 
MadIceAuthor Commented:
That seems to be working. Thanks alot.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now