Row inserts for groups of rows (Totals for groups of like data).

Question

Row inserts for groups of rows (Totals for groups of like data).

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

bswhipp Ten Centuries Points: 1117 More actions · Answer 1

drew.allen - Wednesday, January 10, 2018 11:39 AM
bswhipp - Wednesday, January 10, 2018 11:25 AM
Drew, One other question Is there a way in the code to change the null values to blanks or space(1)?
There are two potential sources for NULL values: the underlying data and NULLs from columns not involved in the current grouping level. You usually want to be able to distinguish between the two. There is a function that can be used to distinguish these. It gives an example of the situation that I'm referring to: GROUPING. You'll want to use that in conjunction with a CASE expression.
Drew

I am specifically talking about the total rows that have the null values except for the totals. I should have stated that to begin with, I apologize.

drew.allen SSC Guru Points: 76985 More actions · Answer 2

bswhipp - Wednesday, January 10, 2018 11:53 AM
drew.allen - Wednesday, January 10, 2018 11:39 AM
bswhipp - Wednesday, January 10, 2018 11:25 AM
Drew, One other question Is there a way in the code to change the null values to blanks or space(1)?
There are two potential sources for NULL values: the underlying data and NULLs from columns not involved in the current grouping level. You usually want to be able to distinguish between the two. There is a function that can be used to distinguish these. It gives an example of the situation that I'm referring to: GROUPING. You'll want to use that in conjunction with a CASE expression.
Drew
I am specifically talking about the total rows that have the null values except for the totals. I should have stated that to begin with, I apologize.

See if you can figure it out from my comments and the example listed in the link above.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

bswhipp - Wednesday, January 3, 2018 6:22 AM
drew.allen - Tuesday, January 2, 2018 1:25 PM
Yes. Use GROUPING SETS. If you want a more complete answer, post sample data and expected results as outlined in the first link in my signature.
This would be much easier in a report tool such as SSRS. Have you considered that?
Drew
OK, I am not getting the results so I will post some data.
The end result should look similar to:
fvendno fcompany POItem PORelsDate........TotStdCost
001275 B&R Industried.............................. 1.00
001275 B&R Industried.............................. 1.00 (total Row)
002119 Roberson Tool................................ 2.00
002119 Roberson Tool................................ 3.00
002119 Roberson Tool................................ 5.00 (total Row)
002302 Unique Automation.......................... 4.00 ( and so on).
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
   DROP TABLE #mytable
--===== Create the test table with
Create table #mytable(
    fvendno char(6),
    fcompany char(50),
    POItemKey char(12),
    PORelsdate datetime,
    totstdcost numeric(15,4),
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
--===== All Inserts into the IDENTITY column
    Set Identity_Insert #mytable ON
--===== Insert the test data into the test table
    Insert into #mytable (fvendno, fcompany, POitemKey, PORelsDate, totstdcost, ID)
    Select '000027', 'Albany Steel', '123456 1', Cast('01/01/2018' as datetime), 1.00, 1 Union all
    Select '000027', 'Albany Steel', '123459 1', Cast('01/01/2018' as datetime), 2.00, 2 Union all
    Select '000060', 'Apple Rubber', '123455 1', Cast('01/01/2018' as datetime), 3.00, 3 Union all
    Select '000060', 'Apple Rubber', '123467 1', Cast('01/01/2018' as datetime), 4.00, 4 Union all
    Select '000080', 'Century Spring', '189456 1', Cast('01/01/2018' as datetime), 5.00, 5 Union all
    Select '000127', 'Everett Manufacturing', '223456 1', Cast('01/01/2018' as datetime), 6.00, 6
--===== Set the identity insert back to normal
    Set Identity_Insert #mytable OFF
    Select * from #mytable

bswhipp - Wednesday, January 10, 2018 11:53 AM
drew.allen - Wednesday, January 10, 2018 11:39 AM
bswhipp - Wednesday, January 10, 2018 11:25 AM
Drew, One other question Is there a way in the code to change the null values to blanks or space(1)?
There are two potential sources for NULL values: the underlying data and NULLs from columns not involved in the current grouping level. You usually want to be able to distinguish between the two. There is a function that can be used to distinguish these. It gives an example of the situation that I'm referring to: GROUPING. You'll want to use that in conjunction with a CASE expression.
Drew
I am specifically talking about the total rows that have the null values except for the totals. I should have stated that to begin with, I apologize.

With that in mind and given the test data above that you posted, what would you like the output to look like? You can use an Excel spreadsheet to depict that, if you'd like.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bswhipp Ten Centuries Points: 1117 More actions · Answer 4

Jeff Moden - Wednesday, January 10, 2018 4:48 PM
bswhipp - Wednesday, January 3, 2018 6:22 AM
drew.allen - Tuesday, January 2, 2018 1:25 PM
Yes. Use GROUPING SETS. If you want a more complete answer, post sample data and expected results as outlined in the first link in my signature.
This would be much easier in a report tool such as SSRS. Have you considered that?
Drew
OK, I am not getting the results so I will post some data.
The end result should look similar to:
fvendno fcompany POItem PORelsDate........TotStdCost
001275 B&R Industried.............................. 1.00
001275 B&R Industried.............................. 1.00 (total Row)
002119 Roberson Tool................................ 2.00
002119 Roberson Tool................................ 3.00
002119 Roberson Tool................................ 5.00 (total Row)
002302 Unique Automation.......................... 4.00 ( and so on).
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
   DROP TABLE #mytable
--===== Create the test table with
Create table #mytable(
    fvendno char(6),
    fcompany char(50),
    POItemKey char(12),
    PORelsdate datetime,
    totstdcost numeric(15,4),
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
--===== All Inserts into the IDENTITY column
    Set Identity_Insert #mytable ON
--===== Insert the test data into the test table
    Insert into #mytable (fvendno, fcompany, POitemKey, PORelsDate, totstdcost, ID)
    Select '000027', 'Albany Steel', '123456 1', Cast('01/01/2018' as datetime), 1.00, 1 Union all
    Select '000027', 'Albany Steel', '123459 1', Cast('01/01/2018' as datetime), 2.00, 2 Union all
    Select '000060', 'Apple Rubber', '123455 1', Cast('01/01/2018' as datetime), 3.00, 3 Union all
    Select '000060', 'Apple Rubber', '123467 1', Cast('01/01/2018' as datetime), 4.00, 4 Union all
    Select '000080', 'Century Spring', '189456 1', Cast('01/01/2018' as datetime), 5.00, 5 Union all
    Select '000127', 'Everett Manufacturing', '223456 1', Cast('01/01/2018' as datetime), 6.00, 6
--===== Set the identity insert back to normal
    Set Identity_Insert #mytable OFF
    Select * from #mytable
bswhipp - Wednesday, January 10, 2018 11:53 AM
drew.allen - Wednesday, January 10, 2018 11:39 AM
bswhipp - Wednesday, January 10, 2018 11:25 AM
Drew, One other question Is there a way in the code to change the null values to blanks or space(1)?
There are two potential sources for NULL values: the underlying data and NULLs from columns not involved in the current grouping level. You usually want to be able to distinguish between the two. There is a function that can be used to distinguish these. It gives an example of the situation that I'm referring to: GROUPING. You'll want to use that in conjunction with a CASE expression.
Drew
I am specifically talking about the total rows that have the null values except for the totals. I should have stated that to begin with, I apologize.
With that in mind and given the test data above that you posted, what would you like the output to look like? You can use an Excel spreadsheet to depict that, if you'd like.

IN the SS, the top is what is from SQL. The bottom is what I would like to see

drew.allen SSC Guru Points: 76985 More actions · Answer 5

As I mentioned, there are two sources of NULLs. You could use the brute force method and treat all NULLs the same or you could use a more finessed approach that allows you to determine whether the NULL is the result of missing data or being a summary record.

/* Brute force method */ SELECT ISNULL(fvendno, '') AS fvendno, ISNULL(fcompany, '') AS fcompany, ISNULL(POItemKey, '') AS POItemKey, ISNULL(CONVERT(CHAR(10), PORelsdate, 120), '') AS PORelsdate, -- Needs to be char to display an empty string. SUM(TotStdCost) AS TotStdCost FROM #mytable GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () ) ;

/* GROUPING method */ SELECT CASE WHEN GROUPING(fvendno) = 1 THEN 'Grand Total' ELSE fvendno END AS fvendno, CASE WHEN GROUPING(fcompany) = 1 THEN '' ELSE fcompany END AS fcompany, CASE WHEN GROUPING(fvendno) = 1 THEN '' WHEN GROUPING(POItemKey) = 1 THEN 'Subtotal' ELSE POItemKey END AS POItemKey, ISNULL(CONVERT(CHAR(10), PORelsdate, 120), '') AS PORelsdate, -- Needs to be char to display an empty string. SUM(TotStdCost) AS TotStdCost FROM #mytable GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () ) ;

Notice that using the CASE expression allows me to differentiate the results for POItemKey column to determine whether the row is a grand total or a subtotal, which is impossible with the brute force approach.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

bswhipp Ten Centuries Points: 1117 More actions · Answer 6

drew.allen - Thursday, January 11, 2018 8:26 AM
As I mentioned, there are two sources of NULLs. You could use the brute force method and treat all NULLs the same or you could use a more finessed approach that allows you to determine whether the NULL is the result of missing data or being a summary record.
/* Brute force method */ SELECT ISNULL(fvendno, '') AS fvendno, ISNULL(fcompany, '') AS fcompany, ISNULL(POItemKey, '') AS POItemKey, ISNULL(CONVERT(CHAR(10), PORelsdate, 120), '') AS PORelsdate, -- Needs to be char to display an empty string. SUM(TotStdCost) AS TotStdCost FROM #mytable GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () ) ;
/* GROUPING method */ SELECT CASE WHEN GROUPING(fvendno) = 1 THEN 'Grand Total' ELSE fvendno END AS fvendno, CASE WHEN GROUPING(fcompany) = 1 THEN '' ELSE fcompany END AS fcompany, CASE WHEN GROUPING(fvendno) = 1 THEN '' WHEN GROUPING(POItemKey) = 1 THEN 'Subtotal' ELSE POItemKey END AS POItemKey, ISNULL(CONVERT(CHAR(10), PORelsdate, 120), '') AS PORelsdate, -- Needs to be char to display an empty string. SUM(TotStdCost) AS TotStdCost FROM #mytable GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () ) ;
Notice that using the CASE expression allows me to differentiate the results for POItemKey column to determine whether the row is a grand total or a subtotal, which is impossible with the brute force approach.
Drew

Ahh, Again, Great Stuff. Thanks you. Never used the Grouping sets so some of the syntax is new to me. Thanks again.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

It also works with GROUP BY WITH ROLLUP and GROUP BY WITH CUBE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

I scanned through this...so maybe I missed something, but I think the OP mentioned that this will be an SSRS report that will then be extracted to Excel. SSRS does a good job of outputting reports to Excel in the format that the report is created.

So - all of this work being done in SQL Server isn't necessary. It can be done in SSRS - much easier where totals can be added at various group levels.

I personally wouldn't spend so much time on creating a query that returned the end result when I knew it could be done in the report...formatted exactly how I wanted it to be extracted to Excel for the end users.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

bswhipp Ten Centuries Points: 1117 More actions · Answer 9

Jeffrey Williams 3188 - Thursday, January 11, 2018 12:34 PM
I scanned through this...so maybe I missed something, but I think the OP mentioned that this will be an SSRS report that will then be extracted to Excel. SSRS does a good job of outputting reports to Excel in the format that the report is created.
So - all of this work being done in SQL Server isn't necessary. It can be done in SSRS - much easier where totals can be added at various group levels.
I personally wouldn't spend so much time on creating a query that returned the end result when I knew it could be done in the report...formatted exactly how I wanted it to be extracted to Excel for the end users.

It is actually a stored procedure for a program to export to excel. Currently the program has an issue exporting to a xlsm and can only export to xlsx. SO I have to get the data looking correct before export otherwise SSRS is the way to go yes.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

bswhipp - Thursday, January 11, 2018 12:46 PM
Jeffrey Williams 3188 - Thursday, January 11, 2018 12:34 PM
I scanned through this...so maybe I missed something, but I think the OP mentioned that this will be an SSRS report that will then be extracted to Excel. SSRS does a good job of outputting reports to Excel in the format that the report is created.
So - all of this work being done in SQL Server isn't necessary. It can be done in SSRS - much easier where totals can be added at various group levels.
I personally wouldn't spend so much time on creating a query that returned the end result when I knew it could be done in the report...formatted exactly how I wanted it to be extracted to Excel for the end users.
It is actually a stored procedure for a program to export to excel. Currently the program has an issue exporting to a xlsm and can only export to xlsx. SO I have to get the data looking correct before export otherwise SSRS is the way to go yes.

You keep talking about some program doing the export to Excel. That means that you're not actually using SSRS to do the export to Excel, correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bswhipp Ten Centuries Points: 1117 More actions · Answer 11

Jeff Moden - Thursday, January 11, 2018 9:41 PM
bswhipp - Thursday, January 11, 2018 12:46 PM
Jeffrey Williams 3188 - Thursday, January 11, 2018 12:34 PM
I scanned through this...so maybe I missed something, but I think the OP mentioned that this will be an SSRS report that will then be extracted to Excel. SSRS does a good job of outputting reports to Excel in the format that the report is created.
So - all of this work being done in SQL Server isn't necessary. It can be done in SSRS - much easier where totals can be added at various group levels.
I personally wouldn't spend so much time on creating a query that returned the end result when I knew it could be done in the report...formatted exactly how I wanted it to be extracted to Excel for the end users.
It is actually a stored procedure for a program to export to excel. Currently the program has an issue exporting to a xlsm and can only export to xlsx. SO I have to get the data looking correct before export otherwise SSRS is the way to go yes.
You keep talking about some program doing the export to Excel. That means that you're not actually using SSRS to do the export to Excel, correct?

some yes some no. it depends on the layout. They select a layout and if it is an excel layout then it just exports the data to excel.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

bswhipp - Friday, January 12, 2018 5:30 AM
Jeff Moden - Thursday, January 11, 2018 9:41 PM
bswhipp - Thursday, January 11, 2018 12:46 PM
Jeffrey Williams 3188 - Thursday, January 11, 2018 12:34 PM
I scanned through this...so maybe I missed something, but I think the OP mentioned that this will be an SSRS report that will then be extracted to Excel. SSRS does a good job of outputting reports to Excel in the format that the report is created.
So - all of this work being done in SQL Server isn't necessary. It can be done in SSRS - much easier where totals can be added at various group levels.
I personally wouldn't spend so much time on creating a query that returned the end result when I knew it could be done in the report...formatted exactly how I wanted it to be extracted to Excel for the end users.
It is actually a stored procedure for a program to export to excel. Currently the program has an issue exporting to a xlsm and can only export to xlsx. SO I have to get the data looking correct before export otherwise SSRS is the way to go yes.
You keep talking about some program doing the export to Excel. That means that you're not actually using SSRS to do the export to Excel, correct?
some yes some no. it depends on the layout. They select a layout and if it is an excel layout then it just exports the data to excel.

So, just to be absolutely clear, if they select an Excel layout, then you have NO SSRS requirements for this problem that you posted, correct? More specifically, you only need the output of a stored procedure for this post, correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bswhipp Ten Centuries Points: 1117 More actions · Answer 13

Jeff Moden - Friday, January 12, 2018 7:06 AM
bswhipp - Friday, January 12, 2018 5:30 AM
Jeff Moden - Thursday, January 11, 2018 9:41 PM
bswhipp - Thursday, January 11, 2018 12:46 PM
Jeffrey Williams 3188 - Thursday, January 11, 2018 12:34 PM
I scanned through this...so maybe I missed something, but I think the OP mentioned that this will be an SSRS report that will then be extracted to Excel. SSRS does a good job of outputting reports to Excel in the format that the report is created.
So - all of this work being done in SQL Server isn't necessary. It can be done in SSRS - much easier where totals can be added at various group levels.
I personally wouldn't spend so much time on creating a query that returned the end result when I knew it could be done in the report...formatted exactly how I wanted it to be extracted to Excel for the end users.
It is actually a stored procedure for a program to export to excel. Currently the program has an issue exporting to a xlsm and can only export to xlsx. SO I have to get the data looking correct before export otherwise SSRS is the way to go yes.
You keep talking about some program doing the export to Excel. That means that you're not actually using SSRS to do the export to Excel, correct?
some yes some no. it depends on the layout. They select a layout and if it is an excel layout then it just exports the data to excel.
So, just to be absolutely clear, if they select an Excel layout, then you have NO SSRS requirements for this problem that you posted, correct? More specifically, you only need the output of a stored procedure for this post, correct?

Correct. This has been a great experience and learning post. I thank everyone involved.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 14

bswhipp - Friday, January 12, 2018 7:25 AM
Correct. This has been a great experience and learning post. I thank everyone involved.

Cool. My questions were based on I was interested because this didn't sound like an SSRS problem and I wanted to make sure that you actually had what you needed. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)