March 27, 2009 at 9:24 am
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.
March 27, 2009 at 10:09 am
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]
March 27, 2009 at 12:28 pm
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://qa.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
http://qa.sqlservercentral.com/articles/cross+tab/65048/
Let us know if you need help along the way.
March 28, 2009 at 2:40 pm
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.
March 28, 2009 at 5:57 pm
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
March 28, 2009 at 6:01 pm
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
April 7, 2009 at 4:03 pm
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
April 7, 2009 at 8:29 pm
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
April 7, 2009 at 8:30 pm
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://qa.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
http://qa.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
April 7, 2009 at 9:19 pm
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
April 8, 2009 at 6:00 am
Excellent thread 🙂
April 14, 2009 at 8:56 am
This code worked swimmingly!! Thank you Jeff and all!!:-P
April 14, 2009 at 10:57 am
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
April 22, 2009 at 1:07 pm
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.
April 23, 2009 at 5:40 pm
Joseph,
Did you ever get this figured out?
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply