Solved

how to pivot dataset

Posted on 2014-02-21
3
177 Views
Last Modified: 2014-03-04
hello,

I have a dataset that looks this way:

current output
I want to be able to display the results this way:

desire output
my output select is:

	SELECT DISTINCT
		[ItemNumber]
	,	[ItemDescription]
	,	[ItemFamilyGroupCode]
	,	[ItemFamilyTypeCode]
	,	[ItemCategoryCode]
	,	[ItemSweetenerGroupCode]
	,	[ItemQuantity]
	,	[SellingPrice]
	,	[MaterialCost]
	,	[LaborCost]
	,	[VariableOverHeadCost]
	,	[ShippingCost]
	,	[OtherCost]
	,	[TotalVariableCost]
	,	[SellingPrice] 
		- [TotalVariableCost] AS [VariableMargin]
	,	[FixedOverHeadCost]
	,	[SGACost]
	,	[TotalVariableCost] 
		+ [FixedOverHeadCost] 
		+ [SGACost] AS [TotalCost]
	,	[NetMargin] = 
					CASE [SellingPrice]
						WHEN 0 THEN 0
						ELSE ([SellingPrice] - [TotalCost]) / [SellingPrice]
					END
	,	[PostingDate]					
	FROM 
		@VariousMarginsTable 
	ORDER BY 
		ItemNumber ASC
	,	PostingDate DESC

Open in new window


how can i obtain the desire out put that is the pivoted output?

any help would be great.

thank a lot!
0
Comment
Question by:metropia
3 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39881410
I do not think you need to PIVOT the data. It looks like that you are trying to UNION different groups of data. I think a simple query as this will achieve what you are after:
;with FGC AS
(
SELECT CASE WHEN GROUPING([ItemFamilyGroupCode])=1 THEN 12 ELSE 11 END AS orderColumn,
CASE WHEN GROUPING([ItemFamilyGroupCode])=1 THEN 'Total' ELSE [ItemFamilyGroupCode] END AS GroupType, 
SUM([ItemQuantity]) AS Quantity
FROM VariousMarginsTable
GROUP BY [ItemFamilyGroupCode] WITH ROLLUP
),
 FTC AS
(
SELECT CASE WHEN GROUPING([ItemFamilyTypeCode])=1 THEN 22 ELSE 21 END AS orderColumn,
CASE WHEN GROUPING([ItemFamilyTypeCode])=1 THEN 'Total' ELSE [ItemFamilyTypeCode] END AS GroupType, 
SUM([ItemQuantity]) AS Quantity
FROM VariousMarginsTable
GROUP BY [ItemFamilyTypeCode] WITH ROLLUP
),
 CC AS
(
SELECT CASE WHEN GROUPING([ItemCategoryCode])=1 THEN 32 ELSE 31 END AS orderColumn,
CASE WHEN GROUPING([ItemCategoryCode])=1 THEN 'Total' ELSE [ItemCategoryCode] END AS GroupType, 
SUM([ItemQuantity]) AS Quantity
FROM VariousMarginsTable
GROUP BY [ItemCategoryCode] WITH ROLLUP
),
 SGC AS
(
SELECT CASE WHEN GROUPING([ItemSweetenerGroupCode])=1 THEN 42 ELSE 41 END AS orderColumn,
CASE WHEN GROUPING([ItemSweetenerGroupCode])=1 THEN 'Total' ELSE [ItemSweetenerGroupCode] END AS GroupType, 
SUM([ItemQuantity]) AS Quantity
FROM VariousMarginsTable
GROUP BY [ItemSweetenerGroupCode] WITH ROLLUP
)
SELECT GroupType, Quantity 
FROM 
(
SELECT 10 as orderColumn,
'Family Group' AS GroupType,
'Quantity' AS Quantity
  UNION ALL
SELECT 20 as orderColumn,
'Family Type' AS GroupType,
'Quantity' AS Quantity
  UNION ALL
SELECT 30 as orderColumn,
'Item Category Code' AS GroupType,
'Quantity' AS Quantity
  UNION ALL
SELECT 40 as orderColumn,
'Sweetener Type' AS GroupType,
'Quantity' AS Quantity
  UNION ALL
SELECT  orderColumn, GroupType, CONVERT(VARCHAR(MAX), Quantity) AS Quantity FROM FGC
  UNION ALL
SELECT  orderColumn, GroupType, CONVERT(VARCHAR(MAX), Quantity) AS Quantity  FROM FTC
  UNION ALL
SELECT  orderColumn, GroupType, CONVERT(VARCHAR(MAX), Quantity) AS Quantity  FROM CC
  UNION ALL
SELECT  orderColumn, GroupType, CONVERT(VARCHAR(MAX), Quantity) AS Quantity  FROM SGC
) AS a ORDER BY orderColumn

Open in new window

SQL Fiddle
0
 

Author Closing Comment

by:metropia
ID: 39904574
Thank you! and sorry for the delay i was pulled to a different task and only now was able to try your solution. it helped me a lot to figure out what I needed to do.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Server running out of memory - Something is consuming all the available memory 17 62
VBScript Write Column Headers 3 38
Run SQL Server Proc from Access 11 31
SQL view 2 27
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question