SQL pivot table grouping

Question

SQL pivot table grouping

Michael M.M. D'Angelo 176

I have the below table structure

With data like below

I want a pivot table that will look like the below in rdl reporting server

Ronen Ariely 15,206 Reputation points

2022-03-01T17:16:08.227+00:00

I have the below table structure

This is NOT A table but an image, which is useless

Please provide queries to create the tables and insert the sample data!

We cannot query the images :-(

In addition please provide the expected result according to the sample data, he version of the SQL Server which you use, and a description of the how you get the expected result set from the source data

Thanks
Michael M.M. D'Angelo 176 Reputation points

2022-03-01T17:19:09.497+00:00

Sorry please

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Rhema_GoldExportSummary](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NOT NULL,
[TotalGrossWeight] [decimal](18, 4) NOT NULL,
[TotalGoldNetWeightOZ] [decimal](18, 4) NOT NULL,
[TotalSilverNetWeightOZ] [decimal](18, 4) NOT NULL,
[TotalGoldSilverValueFunctional] [decimal](18, 2) NOT NULL,
[DateCreated] [smalldatetime] NOT NULL,
[Month] AS (datename(month,[datecreated])),
[Years] AS (datepart(year,[datecreated]))
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_ClientID] DEFAULT ((41)) FOR [ClientID]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_TotalGrossWeight] DEFAULT ((0)) FOR [TotalGrossWeight]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_TotalGoldNetWeightOZ] DEFAULT ((0)) FOR [TotalGoldNetWeightOZ]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_TotalSilverNetWeightOZ] DEFAULT ((0)) FOR [TotalSilverNetWeightOZ]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_TotalGoldSilverValueFunctional] DEFAULT ((0)) FOR [TotalGoldSilverValueFunctional]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO

The expected result is the 3rd image in my earlier post

Also the version is Microsoft SQL Server 2014 - 12.0.4100.1 (X64)

Thank you so much
Ronen Ariely 15,206 Reputation points

2022-03-01T17:27:30.987+00:00

Thanks for providing the queries to create the table, but we still need the data in order to get the expected result set

please provide queries to insert the sample data

Note of you do not know how to get these queries then you can provide the csv file with the sample data

Note: check my first respond. There are more information which we need and can help to provide a solution which fit your needs. Table and sample data is the most important obviously :-)
Michael M.M. D'Angelo 176 Reputation points

2022-03-01T17:32:47.407+00:00

178972-date.txt

Please find attached the script to insert the date.
Thank you

Accepted answer

1 additional answer

Your answer

Ronen Ariely 15,206 Reputation points

2022-03-01T17:16:08.227+00:00

I have the below table structure

This is NOT A table but an image, which is useless

Please provide queries to create the tables and insert the sample data!

We cannot query the images :-(

In addition please provide the expected result according to the sample data, he version of the SQL Server which you use, and a description of the how you get the expected result set from the source data

Thanks
Michael M.M. D'Angelo 176 Reputation points

2022-03-01T17:19:09.497+00:00

Sorry please

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Rhema_GoldExportSummary](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NOT NULL,
[TotalGrossWeight] [decimal](18, 4) NOT NULL,
[TotalGoldNetWeightOZ] [decimal](18, 4) NOT NULL,
[TotalSilverNetWeightOZ] [decimal](18, 4) NOT NULL,
[TotalGoldSilverValueFunctional] [decimal](18, 2) NOT NULL,
[DateCreated] [smalldatetime] NOT NULL,
[Month] AS (datename(month,[datecreated])),
[Years] AS (datepart(year,[datecreated]))
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_ClientID] DEFAULT ((41)) FOR [ClientID]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_TotalGrossWeight] DEFAULT ((0)) FOR [TotalGrossWeight]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_TotalGoldNetWeightOZ] DEFAULT ((0)) FOR [TotalGoldNetWeightOZ]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_TotalSilverNetWeightOZ] DEFAULT ((0)) FOR [TotalSilverNetWeightOZ]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_TotalGoldSilverValueFunctional] DEFAULT ((0)) FOR [TotalGoldSilverValueFunctional]
GO

ALTER TABLE [dbo].[Rhema_GoldExportSummary] ADD CONSTRAINT [DF_Rhema_GoldExportSummary_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO

The expected result is the 3rd image in my earlier post

Also the version is Microsoft SQL Server 2014 - 12.0.4100.1 (X64)

Thank you so much
Ronen Ariely 15,206 Reputation points

2022-03-01T17:27:30.987+00:00

Thanks for providing the queries to create the table, but we still need the data in order to get the expected result set

please provide queries to insert the sample data

Note of you do not know how to get these queries then you can provide the csv file with the sample data

Note: check my first respond. There are more information which we need and can help to provide a solution which fit your needs. Table and sample data is the most important obviously :-)
Michael M.M. D'Angelo 176 Reputation points

2022-03-01T17:32:47.407+00:00

178972-date.txt

Please find attached the script to insert the date.
Thank you

Answer 1

Hi @Michael M.M. D'Angelo
There several ways to this issue:
First: Use aggregate functions along with Case when,just as Naomi answered.However this method need to write multiple lines of code.

Second: Use Cross Apply and Pivot,like this:

;WITH CTE AS  
(  
 SELECT R.ClientID,R.Years,U.*  
 FROM Rhema_GoldExportSummary R  
 CROSS APPLY(VALUES(TotalGrossWeight,SUBSTRING([Month],1,3)+'_TotalGrossWeight'),  
                   (TotalGoldNetWeightOZ,SUBSTRING([Month],1,3)+'_TotalGoldNetWeightOZ'),  
				   (TotalSilverNetWeightOZ,SUBSTRING([Month],1,3)+'_TotalSilverNetWeightOZ'),  
				   (TotalGoldSilverValueFunctional,SUBSTRING([Month],1,3)+'_TotalGoldSilverValueFunctional')) AS U(VALUE,TITLE)  
)  
SELECT * FROM CTE  
PIVOT(MAX(VALUE)FOR TITLE IN([Jan_TotalGrossWeight],[Jan_TotalGoldNetWeightOZ],[Jan_TotalSilverNetWeightOZ],[Jan_TotalGoldSilverValueFunctional],  
                             [Feb_TotalGrossWeight],[Feb_TotalGoldNetWeightOZ],[Feb_TotalSilverNetWeightOZ],[Feb_TotalGoldSilverValueFunctional],  
							 [Mar_TotalGrossWeight],[Mar_TotalGoldNetWeightOZ],[Mar_TotalSilverNetWeightOZ],[Mar_TotalGoldSilverValueFunctional],  
							 [Apr_TotalGrossWeight],[Apr_TotalGoldNetWeightOZ],[Apr_TotalSilverNetWeightOZ],[Apr_TotalGoldSilverValueFunctional],  
							 [May_TotalGrossWeight],[May_TotalGoldNetWeightOZ],[May_TotalSilverNetWeightOZ],[May_TotalGoldSilverValueFunctional],  
							 [Jun_TotalGrossWeight],[Jun_TotalGoldNetWeightOZ],[Jun_TotalSilverNetWeightOZ],[Jun_TotalGoldSilverValueFunctional],  
							 [Jul_TotalGrossWeight],[Jul_TotalGoldNetWeightOZ],[Jul_TotalSilverNetWeightOZ],[Jul_TotalGoldSilverValueFunctional],  
							 [Aug_TotalGrossWeight],[Aug_TotalGoldNetWeightOZ],[Aug_TotalSilverNetWeightOZ],[Aug_TotalGoldSilverValueFunctional],  
							 [Sep_TotalGrossWeight],[Sep_TotalGoldNetWeightOZ],[Sep_TotalSilverNetWeightOZ],[Sep_TotalGoldSilverValueFunctional],  
							 [Oct_TotalGrossWeight],[Oct_TotalGoldNetWeightOZ],[Oct_TotalSilverNetWeightOZ],[Oct_TotalGoldSilverValueFunctional],  
							 [Nov_TotalGrossWeight],[Nov_TotalGoldNetWeightOZ],[Nov_TotalSilverNetWeightOZ],[Nov_TotalGoldSilverValueFunctional],  
							 [Dec_TotalGrossWeight],[Dec_TotalGoldNetWeightOZ],[Dec_TotalSilverNetWeightOZ],[Dec_TotalGoldSilverValueFunctional]))P

Third：Use dynamic SQL to get column names，like this:

DECLARE @COLUMN_NAME VARCHAR(MAX)  
DECLARE @SQL VARCHAR(MAX)  
;WITH CTE AS  
(SELECT R.*,U.*  
 FROM Rhema_GoldExportSummary R  
 CROSS APPLY(VALUES(TotalGrossWeight,SUBSTRING([Month],1,3)+'_TotalGrossWeight'),  
                   (TotalGoldNetWeightOZ,SUBSTRING([Month],1,3)+'_TotalGoldNetWeightOZ'),  
				   (TotalSilverNetWeightOZ,SUBSTRING([Month],1,3)+'_TotalSilverNetWeightOZ'),  
				   (TotalGoldSilverValueFunctional,SUBSTRING([Month],1,3)+'_TotalGoldSilverValueFunctional')) AS U(VALUE,TITLE)  
 WHERE ClientID=40  
)  
SELECT @COLUMN_NAME = ISNULL(@COLUMN_NAME + ',','') +QUOTENAME(TITLE)   
FROM CTE ORDER BY ID  
--PRINT @COLUMN_NAME  
SET @SQL = '  
WITH CTE AS  
(  
 SELECT R.ClientID,R.Years,U.*  
 FROM Rhema_GoldExportSummary R  
 CROSS APPLY(VALUES(TotalGrossWeight,SUBSTRING([Month],1,3)+''_TotalGrossWeight''),  
                   (TotalGoldNetWeightOZ,SUBSTRING([Month],1,3)+''_TotalGoldNetWeightOZ''),  
				   (TotalSilverNetWeightOZ,SUBSTRING([Month],1,3)+''_TotalSilverNetWeightOZ''),  
				   (TotalGoldSilverValueFunctional,SUBSTRING([Month],1,3)+''_TotalGoldSilverValueFunctional'')) AS U(VALUE,TITLE)  
)  
SELECT * FROM CTE  
PIVOT(MAX(VALUE)FOR TITLE IN('+ @COLUMN_NAME +'))P'  
PRINT @SQL  
EXEC (@SQL)

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Michael M.M. D'Angelo 176 Reputation points

2022-03-02T12:40:26.727+00:00

Thank you so much sir.
Please how can I get the totals for each column
Naomi Nosonovsky 8,431 Reputation points

2022-03-02T16:34:22.347+00:00

Take a look at this
https://www.sqlservertutorial.net/sql-server-basics/sql-server-grouping-sets/
LiHong-MSFT 10,056 Reputation points

2022-03-03T03:23:17.827+00:00

Change the CTE like this:

;WITH CTE AS ( SELECT R.ClientID,R.Years,U.TITLE,SUM(u.VALUE)AS VALUE FROM Rhema_GoldExportSummary R CROSS APPLY(VALUES(TotalGrossWeight,SUBSTRING([Month],1,3)+'_TotalGrossWeight'), (TotalGoldNetWeightOZ,SUBSTRING([Month],1,3)+'_TotalGoldNetWeightOZ'), (TotalSilverNetWeightOZ,SUBSTRING([Month],1,3)+'_TotalSilverNetWeightOZ'), (TotalGoldSilverValueFunctional,SUBSTRING([Month],1,3)+'_TotalGoldSilverValueFunctional')) AS U(VALUE,TITLE) GROUP BY GROUPING SETS((R.Years,R.ClientID,U.TITLE),(U.TITLE)) )

And if you want the total in the last row,then add this order by clause in the end of the query

ORDER BY CASE WHEN ClientID IS NULL THEN 1 ELSE 0 END ASC

Answer 2

Naomi Nosonovsky 8,431

Do you want to have 12 big columns (for each month of the year) always or you may want to that 12 to be conditional if the year is in the middle?

For the former there is a T-SQL case based pivot which I normally use. Since you have 4 weights, you would have 48 expressions in your query.

You can also construct it dynamically.

https://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

Michael M.M. D'Angelo 176 Reputation points

2022-03-01T18:17:20.097+00:00

Yes I want 12 big months, but each month has data from the 4 columns
Naomi Nosonovsky 8,431 Reputation points

2022-03-01T18:36:41.927+00:00
Just use case based pivot then, e.g.

select client_no, [Years], sum(case when [Month] = 'January' then Weight1 end) as [JanWeight1], sum(case when [Month] = 'January' then Weight2 end) as [JanWeight2], ... from WeightsInfo GROUP BY ClientID, [Years]

You would need 48 total of such expressions (for each weight) and you can use these columns in your report.
Michael M.M. D'Angelo 176 Reputation points

2022-03-01T19:58:28.663+00:00

This is how it turns up, it doesnt arrange on same line, its more link stepped data, i want one companyname per row
Naomi Nosonovsky 8,431 Reputation points

2022-03-01T20:12:21.303+00:00

Don't include [Month] column in your query. You only need columns like ClientNo and Years and the sum(..) of your weights.

Share via

SQL pivot table grouping

1 additional answer

Your answer