InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 20 Mar 2010 10:27:04 GMT20http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Mike M - DBA2B (2/25/2010)[/b][hr]Ha!Well, you, a fellow named Tom(?), and I all used to try to help folks out on another SQL forum about 6 years ago. Tom was a master of Oracle, you were the SQL Server king, and I'd try to run a little cleanup with my hodgepodge knowledge of things like Access, SQL Server, and MySQL.You and I used to have friendly debates on the merits all-in-one queries versus "bite-sized" temporary sets. It's been years. I'm not *terribly* hurt that you don't remember. ;-)Regards,Mike M[/quote]Oh my... that WAS a long time ago. That was on the "BELUTION" forum which has been offline for many years... probably about 6 just as you mention. Glad to see you around!Thu, 25 Feb 2010 13:05:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHa!Well, you, a fellow named Tom(?), and I all used to try to help folks out on another SQL forum about 6 years ago. Tom was a master of Oracle, you were the SQL Server king, and I'd try to run a little cleanup with my hodgepodge knowledge of things like Access, SQL Server, and MySQL.You and I used to have friendly debates on the merits all-in-one queries versus "bite-sized" temporary sets. It's been years. I'm not *terribly* hurt that you don't remember. ;-)Regards,Mike MThu, 25 Feb 2010 12:11:26 GMTMike M - DBA2Bhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Mike M - DBA2B (2/25/2010)[/b][hr]I saw the title and author of the articles and it seemed like old times. :-)Jeff! It's been years since we last conversed, but it's good to see you're still out and about and helping the SQL community. Some things never change. Great articles!Regards,Mike M[/quote]This is terrible, Mike... I know about 6 "Mike M"s that I've not seen in several years... which one are you?Thu, 25 Feb 2010 11:20:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxI saw the title and author of the articles and it seemed like old times. :-)Jeff! It's been years since we last conversed, but it's good to see you're still out and about and helping the SQL community. Some things never change. Great articles!Regards,Mike MThu, 25 Feb 2010 09:29:26 GMTMike M - DBA2Bhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxVery cool. Glad you could use so much of the article. Thanks for the feedback, Ray.Tue, 19 May 2009 18:25:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxExcellent post, Jeff. The SQL PIVOT operator has always been a complete mystery to me, and my attempt at it today was no better. I found your solution and like it better because I can understand what I wrote without having to look up PIVOT everytime I want to read my own code!I especially liked the trick of accumulating the string value for the variable number of columns by using a SELECT instead of a loop. Very cool. :cool:You've made me a better SQL programmer today, and for that, I thank you.RayTue, 19 May 2009 16:06:27 GMTray-588024http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxThanks, Manie. I sure do appreciate the feedback.Tue, 21 Apr 2009 23:57:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Jeff Moden (4/20/2009)[/b][hr][quote][b]Manie Verster (4/18/2009)[/b][hr]Hi Jeff, you know sometimes you just amaze me! I love your article (got to it a bit late) but I just have one problem. I have not used this type of querying much and when I copy your code it all comes out as gibberish and I don't quite get it right to fix it so nicely. Could you possibly attach your code in a text file or something so I can download it. A .sql file will work even better. I would not like to miss this code since I get a need for this type of query all the time. Keep up the good work![/quote]Thanks for the great feedback, Manie.Many forums have problems with copying code and this one is no exception. There is an "easy" way to do it, though. Position your cursor just above a code window... click and drag to just below the code window to select it all. Both positions must actually be outside the code window.Then, paste to Word. Then, copy all from Word and paste to QA or SSMS. Everything except blank lines will be preserved that way... sans "gibberish".I'll be sure to include all code in a handy text file in future articles.[/quote]Jeff, thanks for the tip and it worked and thanks for a great script and article. You know, a person gets a need for pivots and crosstabs all the time and when you actually create it in the best way you know, it sometimes lack performance. A script like this is just the thing to do these things in a better way.Tue, 21 Apr 2009 23:05:16 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Manie Verster (4/18/2009)[/b][hr]Hi Jeff, you know sometimes you just amaze me! I love your article (got to it a bit late) but I just have one problem. I have not used this type of querying much and when I copy your code it all comes out as gibberish and I don't quite get it right to fix it so nicely. Could you possibly attach your code in a text file or something so I can download it. A .sql file will work even better. I would not like to miss this code since I get a need for this type of query all the time. Keep up the good work![/quote]Thanks for the great feedback, Manie.Many forums have problems with copying code and this one is no exception. There is an "easy" way to do it, though. Position your cursor just above a code window... click and drag to just below the code window to select it all. Both positions must actually be outside the code window.Then, paste to Word. Then, copy all from Word and paste to QA or SSMS. Everything except blank lines will be preserved that way... sans "gibberish".I'll be sure to include all code in a handy text file in future articles.Mon, 20 Apr 2009 20:37:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHi Jeff, you know sometimes you just amaze me! I love your article (got to it a bit late) but I just have one problem. I have not used this type of querying much and when I copy your code it all comes out as gibberish and I don't quite get it right to fix it so nicely. Could you possibly attach your code in a text file or something so I can download it. A .sql file will work even better. I would not like to miss this code since I get a need for this type of query all the time. Keep up the good work!Sat, 18 Apr 2009 00:16:45 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]calston (3/4/2009)[/b][hr]Jeff, this was like a God-sent article to me. It's fantastic. I do need to add a few more groupings, but without your article I would not have been able to complete this project with Dates Cross-tab dynamically. Thanks!!!Cynthia[/quote]That's outstanding news, Cynthia!! Glad to have been a help. Thanks for taking the time to post this wonderful feedback... it's very much appreciated!Wed, 04 Mar 2009 16:12:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxJeff, this was like a God-sent article to me. It's fantastic. I do need to add a few more groupings, but without your article I would not have been able to complete this project with Dates Cross-tab dynamically. Thanks!!!CynthiaWed, 04 Mar 2009 10:50:50 GMTcalstonhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxThat's some good info, Luke. Thanks for sharing it with us.Thu, 19 Feb 2009 08:30:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxJeff 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.Thu, 19 Feb 2009 06:24:22 GMTLuke Lhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]jcrawf02 (2/18/2009)[/b][hr]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[/quote]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.Wed, 18 Feb 2009 22:14:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxJeff, 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 writingWed, 18 Feb 2009 15:27:23 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxThanks - that's exactly what I was looking for!Jonathan :)Mon, 19 Jan 2009 05:55:02 GMTjon.skinhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxMaybe 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 Nametherfore you can use a Min or max in the aggregate[code]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 ImageFROM Temp ) as aGroup byName[/code]Note : I wrote the code like this so you can look at the intermediate resultsMon, 19 Jan 2009 05:22:30 GMTAnzioBakehttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxFollowing 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 ImageINTO #Temp1 FROM Temp ORDER BY NameSELECT * FROM TempSELECT * FROM #Temp1If 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.JonathanMon, 19 Jan 2009 04:28:48 GMTjon.skinhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]jon.skin (1/17/2009)[/b][hr]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 anaggregational 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 Value3INTO t2FROM t1GROUP BY pivotcol2) Updating the target table with a subquery on the source table for each column value :-UPDATE t2SET 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 foundTOP 1 provides protection against unexpected duplicatesThis must be very inefficient and I wonder if there is a better way using some sort of JOIN technique ?Jonathan Skinner[/quote]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.Sat, 17 Jan 2009 13:36:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxThe 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 anaggregational 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 Value3INTO t2FROM t1GROUP BY pivotcol2) Updating the target table with a subquery on the source table for each column value :-UPDATE t2SET 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 foundTOP 1 provides protection against unexpected duplicatesThis must be very inefficient and I wonder if there is a better way using some sort of JOIN technique ?Jonathan SkinnerSat, 17 Jan 2009 05:29:20 GMTjon.skinhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxThe 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 rotationis 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 Value3INTO t2FROM t1GROUP BY pivotcol1, pivotcol22) Updating the target table with a subquery on the source table for each column value :-UPDATE t2SET 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 inefficientSat, 17 Jan 2009 05:20:21 GMTjon.skinhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxCool... I'll take a look. Thanks, Anzio.Wed, 31 Dec 2008 05:48:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHi....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 etcI did not do the Destination table that I had in my original script...just a executeWed, 31 Dec 2008 00:42:53 GMTAnzioBakehttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxThanks, Anzio.Tue, 30 Dec 2008 04:46:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHi. 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 wantTue, 30 Dec 2008 01:58:35 GMTAnzioBakehttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Jeff Moden (12/22/2008)[/b][hr][quote][b]AnzioBake (12/22/2008)[/b][hr]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[/quote]Kewl! Got some code you'd like to share?[/quote]Hello?Tue, 23 Dec 2008 17:59:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]AnzioBake (12/22/2008)[/b][hr]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[/quote]Kewl! Got some code you'd like to share?Mon, 22 Dec 2008 05:01:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxAlthough 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 pivotingMon, 22 Dec 2008 03:10:53 GMTAnzioBakehttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxJeff,It will more than 2 rows, They are dynamic and it will change based on each experiment.In that case, I have to display them as follows.INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (1,12.3,'P',42.5,22)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (1,12.3,'P',32.5,23)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (2,32.3,'P',43.5,22)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (2,8.3,'P',2.5,23)--------------INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (1,12.3,'P',42.5,24)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (1,12.3,'P',32.5,25)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (2,32.3,'P',43.5,24)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (2,8.3,'P',2.5,25)commonId Signal_22 Detect_22 PValue_22 Signal_23 Detect_23 PValue_23 Signal_24 Detect_24 PValue_24 Signal_25 Detect_25 PValue_251 12.3 p 42.5 12.3 P 32.5 12.3 p 42.5 12.3 P 32.52 32.3 P 43.5 8.3 P 2.5 32.3 P 43.5 8.3 P 2.5 Thanks,MariaPSI will copy and paste all output in TSQL forum too.Thu, 18 Dec 2008 08:47:21 GMTpsmg01http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]psmg01 (12/17/2008)[/b][hr]Hi Jeff,Thank you for your reply. I will post this in T_SQL forum too.I am having a table like below[CREATE TABLE [dbo].[Output_Info]( [Common_Id] [int] NOT NULL, [Signal] [real] NOT NULL, [Detect] [nchar](10) NOT NULL, [PValue] [real] NOT NULL, [EAID] [int] NOT NULL,)]INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (1,12.3,'P',42.5,22)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (1,12.3,'P',32.5,23)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (2,32.3,'P',43.5,22)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (2,8.3,'P',2.5,23)For each common_Id, I would like to get the rows as common_Id Signal_EAID,Detect_EAID,PValue_EAIDsO THE result column headers should look like thiscommonId Signal_22 Detect_22 PValue_22 Signal_23 Detect_23 PValue_231 12.3 p 42.5 12.3 P 32.52 32.3 P 43.5 8.3 P 2.5Is it possible to do this? I am confused about this and got stuck here. Please help me.Thanks,-Maria[/quote]Thanks for making it eay to use your data... but we still need to know... are there only going to be two matching rows or not? If there are more than 2, what do you want to do?Again, I wouldn't post here... I would post it on the new thread you started.Wed, 17 Dec 2008 23:38:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]psmg01 (12/17/2008)[/b][hr]Hi Jeff,I have a table that has the following values.col1 | col2 | col3 | col41 | val1| val2 | val32 | val21| val22 | val233 | val31| val32 | val33what I want isfor each col1 entries, I should have the rcol2,col3 and col4 values ascol2_1 col3_1 col4_1 col2_2 col3_2 col4_2 col2_3 col3_3 col4_3val1 |val2 |val3 |val21 |val22 | val23 | val31 | val32 |val33Could you please tell me how to do this?Thanks for your help.-Maria[/quote]Hello Maria,Here's the general approach to this problem using the RAC utility. The idea is to first create a crosstab and then rotate it to get the kind of row you want.[code]create table R1 (col1 int primary key, col2 char(4), col3 char(4), col4 char(4))insert R1 values(1, 'val11', 'val12', 'val13')insert R1 values(2, 'val21', 'val22', 'val23')insert R1 values(3, 'val31', 'val32', 'val33')Exec Rac@transform='Max(col2) as col2 & Max(col3) as col3 & Max(col4) as col4',@rows='rowid',@pvtcol='col1', @from='(select 1 as rowid,col1,col2,col3,col4 from R1) as A',@row_totals='n',@grand_totals='n',@rowbreak='n',@rotate='nest',@shell='no',@racheck='y',@select='select _pvtcols_ from rac'[/code][code]col2_1 col3_1 col4_1 col2_2 col3_2 col4_2 col2_3 col3_3 col4_3------ ------ ------ ------ ------ ------ ------ ------ ------val11 val12 val13 val21 val22 val23 val31 val32 val33[/code]Note the default RAC rotated column names. Great minds must think alike:)Visit RAC @[url=http://www.rac4sql.net][b]www.rac4sql.net[/b][/url][url=http://www.beyondsql.blogspot.com ][b]www.beyondsql.blogspot.com[/b][/url]Wed, 17 Dec 2008 21:30:05 GMTsteve dassinhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]jcraddock (12/17/2008)[/b]It was originally written in ASP, but we re-wrote in in ASP.NET several years ago. We found going to c# to be very hours intensive, so we settled for vb.net.I cannot share the code, as I co-own it with the University. [/quote]So just post your half:SEL col, umn2, olumn3FR ableERE fiel = ield2:PWed, 17 Dec 2008 15:04:56 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]psmg01 (12/17/2008)[/b][hr]Hi SSC,Thanks for your reply.Is there a url for a site, where I can generate sql for crosstab?or How to write the query in my case? I am sorry, I don't understand where to start.I am very new to sqlserver.Please help meThanks,Maria[/quote]Yes... the article which is the source of this thread provides an introduction... ;)Wed, 17 Dec 2008 14:20:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]dphillips (12/17/2008)[/b][hr]I learned the hard way too. After losing many a potential post to timeout, and getting burned and not re-doing all the post, I started doing a quick Ctrl+C (copy) of the post before sending. The reply form times out on me all the time, because I tool with it, get pulled in another direction, and come back, and poof, timeout and loss of my text. Since snagging a copy already has the Quoted section in it, you don't have to go find the response you were quoting, just reply to the article again, and paste.I would be interested in your code... (grin). Is it in old ASP or ASP.NET?[/quote]It was originally written in ASP, but we re-wrote in in ASP.NET several years ago. We found going to c# to be very hours intensive, so we settled for vb.net.I cannot share the code, as I co-own it with the University. I have thought about writing more on the metadata driven approach to web development. I honestly cannot believe there isn't a good source on how to do it anywhere. There is a commercial system available that offers the same basic approach. They do their front-end a bit differently. I actually thought about adopting it, but found it didn't quite meet all my needs.Wed, 17 Dec 2008 13:52:29 GMTjcraddockhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHi SSC,Thanks for your reply.Is there a url for a site, where I can generate sql for crosstab?or How to write the query in my case? I am sorry, I don't understand where to start.I am very new to sqlserver.Please help meThanks,MariaWed, 17 Dec 2008 12:04:58 GMTpsmg01http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]jcraddock (12/17/2008)[/b][hr]Ouch, I wrote a large reply and my session timed out.Here is the abbreviated reply. I couldn't show how to do this in just one article... (snip)[/quote]I learned the hard way too. After losing many a potential post to timeout, and getting burned and not re-doing all the post, I started doing a quick Ctrl+C (copy) of the post before sending. The reply form times out on me all the time, because I tool with it, get pulled in another direction, and come back, and poof, timeout and loss of my text. Since snagging a copy already has the Quoted section in it, you don't have to go find the response you were quoting, just reply to the article again, and paste.I would be interested in your code... (grin). Is it in old ASP or ASP.NET?Wed, 17 Dec 2008 11:59:17 GMTDPhillips-731960http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]jcrawf02 (12/11/2008)[/b][hr][quote][b]Jeff Moden (12/3/2008)[/b][hr][quote][b]jcraddock (12/3/2008)[/b][hr]I've had better luck writing code that writes my SQL. I wrote a set of code that you point at any table, it accepts any column within the table as the top or left portion of the crosstab and any numeric column as the data portion. Another option is to calculate as percent of the whole or sum or count. Doing it that way, you have one set of reusable code for all crosstabs...works pretty well. I usually just create a simple view, point the object at the view and Voila have instant reconfigurable, groupable by anything crosstab. I even added an option to subgroup on the left side.Same concept, more code up front, but infinitely reusable.[/quote]I absolutely agree with that, Jim. I was going to try to write some code to do such a thing as you describe, but you beat me to it... probably by years.I think folks would give up an eye-tooth to see such code. You haven't written an article since February of 2005... maybe it's time to put pen to paper again. It sounds like the "Rosetta Stone" for all dynamic cross-tabs and I know that I'd seriously like to see that code in an article...[/quote]I think I'm out of eye-teeth already, but motion seconded. All in favor?[/quote]Ouch, I wrote a large reply and my session timed out.Here is the abbreviated reply. I couldn't show how to do this in just one article because the code is mostly in ASP.NET. This is because it is written on the foundation of the metadata based/data driven model I discussed in my original article. However, you could do the same thing mostly in a stored procedure, but the front-end would still need coded in something.Here are some screenshots of the crosstab portion of my applicationThis is a simple output of a query (the query is shown at the top for me as a debug tool, other users don't see it.)[img]http://www.craddockfamily.com/images/ctabs/crosstab1.jpg[/img]This is page I use to record the meta data about the report.[img]http://www.craddockfamily.com/images/ctabs/crosstab2.jpg[/img]This is where I record which columns can be filtered on.[img]http://www.craddockfamily.com/images/ctabs/crosstab3.jpg[/img]And here is an example of a complex query it automagically generates on a more complex view.[img]http://www.craddockfamily.com/images/ctabs/crosstab4.jpg[/img]All that is required for a new crosstab is to load the metadata seen in shots two and three.Wed, 17 Dec 2008 11:38:00 GMTjcraddockhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHi Jeff,Thank you for your reply. I will post this in T_SQL forum too.I am having a table like below[CREATE TABLE [dbo].[Output_Info]( [Common_Id] [int] NOT NULL, [Signal] [real] NOT NULL, [Detect] [nchar](10) NOT NULL, [PValue] [real] NOT NULL, [EAID] [int] NOT NULL,)]INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (1,12.3,'P',42.5,22)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (1,12.3,'P',32.5,23)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (2,32.3,'P',43.5,22)INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID]) VALUES (2,8.3,'P',2.5,23)For each common_Id, I would like to get the rows as common_Id Signal_EAID,Detect_EAID,PValue_EAIDsO THE result column headers should look like thiscommonId Signal_22 Detect_22 PValue_22 Signal_23 Detect_23 PValue_231 12.3 p 42.5 12.3 P 32.52 32.3 P 43.5 8.3 P 2.5Is it possible to do this? I am confused about this and got stuck here. Please help me.Thanks,-MariaWed, 17 Dec 2008 08:52:24 GMTpsmg01http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]psmg01 (12/17/2008)[/b][hr]Hi Jeff,I have a table that has the following values.col1 | col2 | col3 | col41 | val1| val2 | val32 | val21| val22 | val233 | val31| val32 | val33what I want isfor each col1 entries, I should have the rcol2,col3 and col4 values ascol2_1 col3_1 col4_1 col2_2 col3_2 col4_2 col2_3 col3_3 col4_3val1 |val2 |val3 |val21 |val22 | val23 | val31 | val32 |val33Could you please tell me how to do this?Thanks for your help.-Maria[/quote]Hi Maria,First, I'd post this in one of the T-SQL forums on this site instead of as part of an article discussion... it'll get more attention than just from me that way.Second, there's some missing information... for example, does the table only have 3 rows or are there more? If more, how do you want those handled. Is column 1 always going to be an unbroken sequence of integers?With that in mind, we likely need more rows of data to give you a nice, tested example that actually works. Do to that, please read the article in the link contained in my signature below... a CREATE TABLE statement and usable INSERT statements go a long way to getting this type of question answered very quickly and accurately.Thanks.Wed, 17 Dec 2008 08:07:59 GMTJeff Moden