Crosstab type report -- Horizontal Summray

Question

Crosstab type report -- Horizontal Summray

Joseph Henry

SSChasing Mays

Points: 626
More actions
March 27, 2009 at 9:24 am

#219475

Hello All:
I'm trying to build a stored procedure to present user data in a fashion that I have not used before. I feel like there must be an easy way to do this, but so far I've barked up several of the wrong trees.
I'm attaching an excel file that illustrates what I'm trying to do. More specifically, what I would like to achieve with the help of the SQL server central community.
The 1st tab is the table to be queried. The 2nd and 3rd tabs are the resultant queries I would like to end up with.
I have a similar post for this in UtterAccess, but I would rather handle it through a stored procedure.
Thanks in advance for any help.

Viewing 15 posts - 1 through 15 (of 20 total)

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

bc_ Hall of Fame Points: 3168 More actions · Answer 1

Is Reporting Services an option? Because its incredibly easy using a straight forward query and letting RS group your data in a matrix.

Otherwise take a look at the sql command PIVOT.

Let us know if you need further help.

bc

[font="Arial Narrow"]bc[/font]

Maxim Picard SSCrazy Points: 2052 More actions · Answer 2

You can find two excellent articles from Jeff Moden on cross tabs and pivots here that should help you tackle this challenge.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

http://www.sqlservercentral.com/articles/T-SQL/63681/

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

http://www.sqlservercentral.com/articles/cross+tab/65048/

Let us know if you need help along the way.

Joseph Henry SSChasing Mays Points: 626 More actions · Answer 3

Thanks for all of the advice. I'm wondering though.. Can I use Pivot without having aggregate functions? I started to work on a select statement, but I only have it on my work PC. Plus it errors out.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Joseph Henry (3/28/2009)
Thanks for all of the advice. I'm wondering though.. Can I use Pivot without having aggregate functions? I started to work on a select statement, but I only have it on my work PC. Plus it errors out.

The aggregates are necessary and, if done properly, will still return only single items.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Sorry... almost forgot... spreadsheets and plain text are a bit of a chore because most folks on this forum like to test their code against your good data before posting it. However, because some of them post so many answers, they simply won't take the time to load a spreadsheet into a table or reformat plain text as Insert/Select statements. Expected results are just fine as a spreadsheet or plain text.

If you want some really good tested code answers to your question, take a look at the article linked in my signature line below. If you post both the table creation and the data that way, people will jump through flaming hoops to help you.

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

Joseph Henry SSChasing Mays Points: 626 More actions · Answer 6

Ok All:

I'm back. Thanks to Jeff for pointing me in the right direction as far as posting goes. Thanks to everyone else for the suggestions so far.

I'm posting code to create my tables as Jeff suggested.

I still have the same goal in mind as my previous posts. Please let me know what you think or if you have any additional information.

Thanks in advance for any help.

Joe

--Post to SQL Server Central

--===== If the test table already exists, drop it

IF OBJECT_ID('Task..#CrossTabEr','U') IS NOT NULL

DROP TABLE #CrossTabEr

--Create the Test Table

CREATE TABLE [dbo].[#CrossTabEr](

[PK_CrossTabErr] [int] IDENTITY(1,1) NOT NULL,

[IdNum] [int] NULL,

[Iteration] [int] NULL,

[Thing] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Things] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Thangs] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

--End Table Creation

--Insert Data Into Table

--Allow IdentityInsert

SET IDENTITY_INSERT #CrossTabEr ON

INSERT INTO #CrossTabEr

([PK_CrossTabErr],[IdNum],[Iteration],[Thing],[Things],[Thangs])

SELECT '1','1','1','Rand1','Rand001','Rand0001' UNION ALL

SELECT '2','2','1','Rand2','Rand002','Rand0002' UNION ALL

SELECT '3','3','1','Rand3','Rand003','Rand0003' UNION ALL

SELECT '4','4','1','Rand4','Rand004','Rand0004' UNION ALL

SELECT '5','5','1','Rand5','Rand005','Rand0005' UNION ALL

SELECT '6','1','2','Rand6','Rand006','Rand0006' UNION ALL

SELECT '7','2','2','Rand7','Rand007','Rand0007' UNION ALL

SELECT '8','3','2','Rand8','Rand008','Rand0008' UNION ALL

SELECT '9','4','2','Rand9','Rand009','Rand0009' UNION ALL

SELECT '10','5','2','Rand10','Rand010','Rand0010' UNION ALL

SELECT '11','1','3','Rand11','Rand011','Rand0011' UNION ALL

SELECT '12','2','3','Rand12','Rand012','Rand0012' UNION ALL

SELECT '13','3','3','Rand13','Rand013','Rand0013' UNION ALL

SELECT '14','4','3','Rand14','Rand014','Rand0014' UNION ALL

SELECT '15','5','3','Rand15','Rand015','Rand0015' UNION ALL

SELECT '16','1','4','Rand16','Rand016','Rand0016' UNION ALL

SELECT '17','2','4','Rand17','Rand017','Rand0017' UNION ALL

SELECT '18','3','4','Rand18','Rand018','Rand0018' UNION ALL

SELECT '19','4','4','Rand19','Rand019','Rand0019' UNION ALL

SELECT '20','5','4','Rand20','Rand020','Rand0020'

SET IDENTITY_INSERT #CrossTabEr OFF

--No More IdentityInsert

--End Insert Data Into Table

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

Joseph Henry (4/7/2009)
I'm posting code to create my tables...

Now we're cooking with gas! Well done, Joseph. Lemme see what I can do.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Maxim Picard (3/27/2009)
You can find two excellent articles from Jeff Moden on cross tabs and pivots here that should help you tackle this challenge.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
http://www.sqlservercentral.com/articles/cross+tab/65048/
Let us know if you need help along the way.

I didn't see this before, Maxim. Thanks for the kudo... :blush:

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Ok... here we go. I copied your very fine test data code and added the answer to it...

[font="Courier New"]--Post to SQL Server Central

--===== If the test table already exists, drop it

IF OBJECT_ID('Task..#CrossTabEr','U') IS NOT NULL

DROP TABLE #CrossTabEr

--===== Create the Test Table

CREATE TABLE [dbo].[#CrossTabEr](

[PK_CrossTabErr] [int] IDENTITY(1,1) NOT NULL,

[IdNum] [int] NULL,

[Iteration] [int] NULL,

[Thing] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Things] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Thangs] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

)

--===== INSERT Data Into Table

-- Allow Identity INSERT

SET IDENTITY_INSERT #CrossTabEr ON

INSERT INTO #CrossTabEr

([PK_CrossTabErr],[IdNum],[Iteration],[Thing],[Things],[Thangs])

SELECT '1','1','1','Rand1','Rand001','Rand0001' UNION ALL

SELECT '2','2','1','Rand2','Rand002','Rand0002' UNION ALL

SELECT '3','3','1','Rand3','Rand003','Rand0003' UNION ALL

SELECT '4','4','1','Rand4','Rand004','Rand0004' UNION ALL

SELECT '5','5','1','Rand5','Rand005','Rand0005' UNION ALL

SELECT '6','1','2','Rand6','Rand006','Rand0006' UNION ALL

SELECT '7','2','2','Rand7','Rand007','Rand0007' UNION ALL

SELECT '8','3','2','Rand8','Rand008','Rand0008' UNION ALL

SELECT '9','4','2','Rand9','Rand009','Rand0009' UNION ALL

SELECT '10','5','2','Rand10','Rand010','Rand0010' UNION ALL

SELECT '11','1','3','Rand11','Rand011','Rand0011' UNION ALL

SELECT '12','2','3','Rand12','Rand012','Rand0012' UNION ALL

SELECT '13','3','3','Rand13','Rand013','Rand0013' UNION ALL

SELECT '14','4','3','Rand14','Rand014','Rand0014' UNION ALL

SELECT '15','5','3','Rand15','Rand015','Rand0015' UNION ALL

SELECT '16','1','4','Rand16','Rand016','Rand0016' UNION ALL

SELECT '17','2','4','Rand17','Rand017','Rand0017' UNION ALL

SELECT '18','3','4','Rand18','Rand018','Rand0018' UNION ALL

SELECT '19','4','4','Rand19','Rand019','Rand0019' UNION ALL

SELECT '20','5','4','Rand20','Rand020','Rand0020'

SET IDENTITY_INSERT #CrossTabEr OFF

--No More Identity INSERT

--End INSERT Data Into Table

--==================================================================================================================

-- Solution to the third tab on the spreadsheet

--==================================================================================================================

--===== Declare some variables to hold the different parts of the dynamic SQL

DECLARE @SQLSELECT VARCHAR(MAX),

@SQLCrossTab VARCHAR(MAX),

@SQLFrom VARCHAR(MAX)

--===== Create the " SELECT" SQL

SELECT @SQLSELECT = ' SELECT IDNum,' + CHAR(10)

--===== Create the multi-section, multi-line cross tab SQL.

-- This works for just about any number of "Iterations"

SELECT @SQLCrossTab = ISNULL(@SQLCrossTab + ',' + CHAR(10) + SPACE(8), SPACE(8))

+ 'MAX(CASE WHEN Iteration = ' + CAST(Iteration AS VARCHAR(3)) + ' THEN Thing END) AS Thing_'

+ CAST(Iteration AS VARCHAR(3)) + ',' + CHAR(10) + SPACE(8)

+ 'MAX(CASE WHEN Iteration = ' + CAST(Iteration AS VARCHAR(3)) + ' THEN Things END) AS Things_'

+ CAST(Iteration AS VARCHAR(3)) + ',' + CHAR(10) + SPACE(8)

+ 'MAX(CASE WHEN Iteration = ' + CAST(Iteration AS VARCHAR(3)) + ' THEN Thangs END) AS Thangs_'

+ CAST(Iteration AS VARCHAR(3))

FROM #CrossTabEr

GROUP BY Iteration

ORDER BY Iteration

--===== Create the rest of the SQL from the "From" on down

SELECT @SQLFrom = '

FROM #CrossTabEr

GROUP BY IDNum

ORDER BY IDNum'

--===== Display the SQL that will be executed (can be commented out for production, of course)

PRINT @SQLSELECT + @SQLCrossTab + @SQLFrom

--===== Execute the SQL to give the desired result.

EXEC (@SQLSELECT + @SQLCrossTab + @SQLFrom)

[/font]

===========================================================================================================================================

The problem with people doing these types of things and then asking if they can do it "without aggragates"

because they're working with text is that they forget than MAX() is an aggragate and that it works on

character based things as well as numerics.

I'd tell you how all of this works, but I've already written the details in an article that Maxim pointed out above.

Here it is again, just for convenience...

[font="Arial Black"]Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/font][/url]

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

Maxim Picard SSCrazy Points: 2052 More actions · Answer 10

Maxim Picard

SSCrazy

Points: 2052

April 8, 2009 at 6:00 am

#974185

Excellent thread 🙂

Joseph Henry SSChasing Mays Points: 626 More actions · Answer 11

This code worked swimmingly!! Thank you Jeff and all!!:-P

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

Thank you, Sir. I appreciate the feedback.

Just in case anyone else reads this thread... see what happens when you give us a little data in a readily consumable format? It's worth learning how to post. As a reminder to everyone, take a look at the link in my signature... it's tells you how to get absolutely the best and quickest answers for almost all code problems. Help us help you and you'll be amazed.

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

Joseph Henry SSChasing Mays Points: 626 More actions · Answer 13

Hello Again Everybody:

I'm trying to take this whole thing a step further. I would like to turn this into a stored procedure for use in my .adp front end. Before I even got stumped with changing this to a stored procedure, I got find problems using a stored procedure in the .adp. Apparently, you cannot pass parameters to a stored procedure in VBA and open it. (At least no way that I've found)

So, what I was thinking was to set up this code as a stored procedure that would create a temporary view that would then open for the user.

Please let me know if this is a good idea or if anyone knows of another approach that might work in my predicament.

If you need more info, please let me know.

As always, thanks in advance for any help that might be provided.

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

Joseph,

Did you ever get this figured out?

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