December 23, 2008 at 5:59 pm
Jeff Moden (12/22/2008)
AnzioBake (12/22/2008)
Although I know the value this article can represent you can actually be far more generic than you suggest.You can write an SP that given the Source Table or View, the Key Columns, and the "value" column and Aggregate can produce the Cross tab query (and execute it)....
This is a method (query) I have been using for several years to do cross Tabs. It is Also why I dislike the the MS version of Pivot as you have to know what is in the column you are pivoting
Kewl! Got some code you'd like to share?
Hello?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2008 at 1:58 am
Hi. Sorry For only responding now. Between Work stress and trying to get the holiday mood I have not responded to this thread.
Yes I will share, but need to get this from home. The original was written for sql2k so is due for a rewrite.
I will endevour to explain the basic idea so people can recreate to SP any which way they want
December 30, 2008 at 4:46 am
Thanks, Anzio.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2008 at 12:42 am
Hi....I decided last night too do a bit of a rewrite of the Procedure for 2005. It should demonostrate that you can get very creative with these scripts.
Some Notes:
I did not do checks like
that Source table/ view /columns exist
the number of values columns produced
Aggregates functions are valid
etc
I did not do the Destination table that I had in my original script...just a execute
December 31, 2008 at 5:48 am
Cool... I'll take a look. Thanks, Anzio.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2009 at 5:20 am
The examples of crosstabbing given are very good by only work with numeric crosscasting.
If the data items in the rows are varchar the only way I have managed to achieve the rotation
is by:-
1) Creating a target table based on grouping the pivotal columns at the same time as creating
new, blank columns for the rotated data :-
SELECT pivotcol1, pivotcol2,
SPACE(50) AS Value1,
SPACE(50) AS Value2,
SPACE(50) AS Value3
INTO t2
FROM t1
GROUP BY pivotcol1, pivotcol2
2) Updating the target table with a subquery on the source table for each column value :-
UPDATE t2
SET Value1 = ISNULL((SELECT TOP 1 Value FROM t1 WHERE id=1), ' '),
Value1 = ISNULL((SELECT TOP 1 Value FROM t1 WHERE id=1), ' '),
This must be very inefficient
January 17, 2009 at 5:29 am
The examples of crosstabbing given are very good by only work with numeric crosscasting.
If you try a similar method with textual columns you cannot use GROUP BY because you need an
aggregational function for the textual columns.
In this circumstance the only way I have managed to achieve the rotation is by:-
1) Creating a target table based on grouping the pivotal column(s) at the same time as creating
new, blank columns which are to be popuated with rotated data :-
SELECT pivotcol,
SPACE(50) AS Value1,
SPACE(50) AS Value2,
SPACE(50) AS Value3
INTO t2
FROM t1
GROUP BY pivotcol
2) Updating the target table with a subquery on the source table for each column value :-
UPDATE t2
SET Value1 = ISNULL((SELECT TOP 1 Value FROM t1 WHERE id=1 AND t2.pivotcol=t1.pivotcol), ' '),
Value2 = ISNULL((SELECT TOP 1 Value FROM t1 WHERE id=2 AND t2.pivotcol=t1.pivotcol), ' '),
Value3 = ISNULL((SELECT TOP 1 Value FROM t1 WHERE id=3 AND t2.pivotcol=t1.pivotcol), ' ')
ISNULL provides a default value where no match is found
TOP 1 provides protection against unexpected duplicates
This must be very inefficient and I wonder if there is a better way using some sort of JOIN technique ?
Jonathan Skinner
January 17, 2009 at 1:36 pm
jon.skin (1/17/2009)
The examples of crosstabbing given are very good by only work with numeric crosscasting.If you try a similar method with textual columns you cannot use GROUP BY because you need an
aggregational function for the textual columns.
In this circumstance the only way I have managed to achieve the rotation is by:-
1) Creating a target table based on grouping the pivotal column(s) at the same time as creating
new, blank columns which are to be popuated with rotated data :-
SELECT pivotcol,
SPACE(50) AS Value1,
SPACE(50) AS Value2,
SPACE(50) AS Value3
INTO t2
FROM t1
GROUP BY pivotcol
2) Updating the target table with a subquery on the source table for each column value :-
UPDATE t2
SET Value1 = ISNULL((SELECT TOP 1 Value FROM t1 WHERE id=1 AND t2.pivotcol=t1.pivotcol), ' '),
Value2 = ISNULL((SELECT TOP 1 Value FROM t1 WHERE id=2 AND t2.pivotcol=t1.pivotcol), ' '),
Value3 = ISNULL((SELECT TOP 1 Value FROM t1 WHERE id=3 AND t2.pivotcol=t1.pivotcol), ' ')
ISNULL provides a default value where no match is found
TOP 1 provides protection against unexpected duplicates
This must be very inefficient and I wonder if there is a better way using some sort of JOIN technique ?
Jonathan Skinner
I've found that cross-tabs are very good at such a thing... but, as you pointed out, not that way. Could you post some data (in the form of INSERT/SELECT statements, please) and a table definition according to the link below in my signature so I can show how? Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2009 at 4:28 am
Following on from my query in regards of handling rotation of textual data :-
CREATE TABLE [dbo].[Temp](
[metaid] [int] NULL,
[label] [varchar](50) NULL,
[value] [varchar](260) NULL,
[name] [varchar] (10)
)
INSERT INTO Temp VALUES (3140, 'Recorded By', 'TJ', '6000')
INSERT INTO Temp VALUES (3187, 'Comments', ' ', '6000')
INSERT INTO Temp VALUES (3181, 'Date', '06/10/', '6000')
INSERT INTO Temp VALUES (1756, 'Path', 'd:\IntrasisExplorerData\DoverCastle5628\Photos\Site Photos\06102008', '6000')
INSERT INTO Temp VALUES (148, 'FileName', '5628_06000.JPG', '6000')
INSERT INTO Temp VALUES (3207, 'Technical comments', '2 x 2 metre scales', '6000')
INSERT INTO Temp VALUES (3199, 'Scales used', '2 metres', '6000')
INSERT INTO Temp VALUES (3189, 'Facing', 'S', '6000')
INSERT INTO Temp VALUES (3549, 'photo type', 'Context record shot', '6000')
INSERT INTO Temp VALUES (3136, 'Site subdivision', 'Trench A', '6000')
INSERT INTO Temp VALUES (3186, 'Textfield', NULL, '6000')
INSERT INTO Temp VALUES (151, 'Image', NULL, '6000')
INSERT INTO Temp VALUES (3186, 'Textfield', NULL, '6001')
INSERT INTO Temp VALUES (151, 'Image', NULL, '6001')
INSERT INTO Temp VALUES (3207, 'Technical comments', '2 x 2 metre scales', '6001')
INSERT INTO Temp VALUES (3187, 'Comments', 'Working Shot', '6001')
INSERT INTO Temp VALUES (3181, 'Date', '06/10/', '6001')
INSERT INTO Temp VALUES (3140, 'Recorded By', 'TJ', '6001')
INSERT INTO Temp VALUES (1756, 'Path', 'd:\IntrasisExplorerData\DoverCastle5628\Photos\Site Photos\06102008', '6001')
INSERT INTO Temp VALUES (148, 'FileName', '5628_06001.JPG', '6001')
INSERT INTO Temp VALUES (3199, 'Scales used', '2 metres', '6001')
INSERT INTO Temp VALUES (3189, 'Facing', 'SE', '6001')
INSERT INTO Temp VALUES (3549, 'photo type', 'Working shot', '6001')
INSERT INTO Temp VALUES (3136, 'Site subdivision', 'Trench A', '6001')
SELECT DISTINCT Name,
SPACE(100) AS Comments,
SPACE(100) As Date,
RecordedBy=SPACE(100),
Path=SPACE(100),
Filename=SPACE(100) ,
TechnicalComments=SPACE(100),
ScalesUsed=SPACE(100),
Facing=SPACE(100),
PhotoType=SPACE(100),
SiteSubdivision=SPACE(100),
TextField=SPACE(100),
SPACE(100) AS Image
INTO #Temp1
FROM Temp
ORDER BY Name
SELECT * FROM Temp
SELECT * FROM #Temp1
If you run the above, #Temp1 shows the shape of the query that I require from Temp
(ie grouped by name)
Therefore the pivot column is Temp.Name and #Temp1 should have 2 rows, the Column values coming
from Temp.value and being identified by Temp.MetaId which is aligned with Temp.label,
the latter being the same or similar to the target column names.
An efficient method would be most appreciated.
Jonathan
January 19, 2009 at 5:22 am
Maybe you should check the script I attached in an earlier response on this thread (at the end of page 8.....
Based on your example data and previous post....
a case statement will produce only one value in the column per Name
therfore you can use a Min or max in the aggregate
Select
Name
, Comments = Min( Comments )
, Date = Min( Date )
, Facing = Min( Facing )
, FileName = Min( FileName )
, Image = Min( Image )
, Path = Min( Path )
, phototype = Min( phototype )
, RecordedBy = Min( RecordedBy )
, Scalesused = Min( Scalesused )
, Sitesubdivision = Min( Sitesubdivision )
, Technicalcomments = Min( Technicalcomments )
, Textfield = Min( Textfield )
From(
SELECT
Name,
Comments = ( Case When Label = 'Comments' Then Value End ),
( Case When Label = 'Date' Then Value End ) As Date,
( Case When Label = 'Recorded By' Then Value End ) as RecordedBy,
( Case When Label = 'Path' Then Value End ) as path,
( Case When Label = 'Filename' Then Value End ) as Filename ,
( Case When Label = 'Technical Comments' Then Value End ) as TechnicalComments,
( Case When Label = 'Scales Used' Then Value End )as ScalesUsed,
( Case When Label = 'Facing' Then Value End )as Facing ,
( Case When Label = 'PhotoType' Then Value End )as PhotoType ,
( Case When Label = 'Site Subdivision' Then Value End ) as SiteSubDivision ,
( Case When Label = 'TextField' Then Value End ) as TextField,
( Case When Label = 'Image' Then Value End ) AS Image
FROM Temp
) as a
Group by
Name
Note : I wrote the code like this so you can look at the intermediate results
January 19, 2009 at 5:55 am
Thanks - that's exactly what I was looking for!
Jonathan 🙂
February 18, 2009 at 3:27 pm
Jeff, I just made myself feel a whole lot better today, since I was helping someone work through a Pivot, and ended up doing much of what you demonstrate here, without realizing how much of this I had absorbed on various read-throughs, and without referencing your article. (I've read this 4 or 5 times, and only today did I actually understand it all)
However, after we figured out what we were trying to do, someone said that in SSRS you can pull your dataset without pivot/crosstabbing, and then create a 'matrix report' that will display in the same manner.
Do you/anyone else have experience with matrix reports? Are they truly comparable? Or is this person setting themselves up for a performance hog compared to your solution?
Thanks again for your excellent writing
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
February 18, 2009 at 10:14 pm
jcrawf02 (2/18/2009)
Jeff, I just made myself feel a whole lot better today, since I was helping someone work through a Pivot, and ended up doing much of what you demonstrate here, without realizing how much of this I had absorbed on various read-throughs, and without referencing your article. (I've read this 4 or 5 times, and only today did I actually understand it all)However, after we figured out what we were trying to do, someone said that in SSRS you can pull your dataset without pivot/crosstabbing, and then create a 'matrix report' that will display in the same manner.
Do you/anyone else have experience with matrix reports? Are they truly comparable? Or is this person setting themselves up for a performance hog compared to your solution?
Thanks again for your excellent writing
I have no grand experience with SSRS because I'm mostly a heavy lifter for ETL and backend batch processing code. However, I've seen enough posts to get the glimmer that SSRS matricies are quite fast (reportedly comparable to properly formed Cross-Tabs) and fairly easy to setup. I haven't seen any actual testing on them, though, and I wouldn't be surprised if pre-aggregated cross-tabs were able to edge them out by just a small margin. Just a gut feel there and I certainly could be wrong. Guess I'll have to teach myself SSRS and give it a whirl. I'd also be interested in just how "easy" it really is... PIVOTs were supposed to be easier than cross-tabs and we've all seen how that turned out. 😉
By the way, thank you for the very thoughtful comments and posts... and I don't mean just on this thread. You're definitely one of the "good guys" and you've helped lots of folks. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2009 at 6:24 am
Jeff and J, like Jeff I'm no expert where it comes to SSRS, but I have done a decent amount of developing with them. While I'd doubt that matrixes would be faster than a pure TSQL solution, they are IMHO much easier and more flexible allowing you to drill to and reorganize your data on the fly and such. But it's the same old story, I can send 100 rows across the network and build the cross tab in SSRS or I can do it in TSQL and send 5 rows... Obviously as that scales you can see where things may start to get sticky.
Additionally, I have found some instances where I just couldn't get the matrix to supply what I needed (odd totals and such) so I did it in the backend query. From what I understand most of the issues with matrices have been corrected with SSRS 2008 tablixes but I haven't had any exposure to those yet.
-Luke.
February 19, 2009 at 8:30 am
That's some good info, Luke. Thanks for sharing it with us.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 76 through 90 (of 130 total)
You must be logged in to reply to this topic. Login to reply