Quick query without #temp tables which uses a CTE (Common Table Expression). Note that the joins have been changed from ANSI-89 and table aliases added.

😎

;WITH GROUP_SESSIONS AS

(

...

Jeff Moden (11/16/2014)

---

Careful now... If there are 3 column pairs for AirCraft and City, each pair of which is supposed to return a single row (i.e. 3 un-pivoted rows for...

I made the mistake of using an HTML tag name in the code, it disappears from the code after it is posted. Here is the code again with a different...

Quick suggestion for a solution, many ways of doing this though

😎

DECLARE @ProductID XML;

SET @ProductID = CONVERT(XML,REPLACE(

'

' + '2674,2711,2756,2818,2845,2889,2891,2920,3623,3768,3866,3878,3889,4084,4091,4113,4742,4753,5310,5313,5505,5512,5542,5569,5620,5781,5926,6012,6082,6090,6091,6117,6118,6207,6226,6233,6298,6316,6383,6477,6523,6589,6601,6715,6728,6785,7216,7326,8096,8643,9836,10335,10387,10403,10957,11007,11031,11184,11490,11722,11857,12105,12121,12126,12130,12190,12744,13144,13523,13665,13693,14865,15293,15294,15302,15724,15784,16175,16307,16843,16846,16995,17184,18133,18877,18911,19263,19402,19666,19955,20601,21484,22620,22622,23298,24815,24821,25148,26194,26976,27078,27333,27572,27761,27803,27806,27825,27830,29380,29478,30278,31263,31742,31745,32548,32625,32719,33525,33565,34581,34674,34861,34910,35415,35780,35999,37080,38366,38953,39843',',','

') + '',0);

;WITH PRODUCT_IDS AS

(

SELECT

...

😀

As you know, I'm one of those people that absolutely love Tally-Table-like constructs but, as I've also said, it's not a panacea of performance. ...

I cannot resist pitching in another method using a Tally type cross-tab, which performs at least as good as the method Jeff posted, although it doesn't result in a parallel...

allenb-717661 (11/14/2014)

---

I'm using 2008 R2 and I have a column with a test field. I know that there are 497 records in the field because when I use a translator...

Quick thought, any row with an...

Quick suggestion for a dynamic cross-tab solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;

CREATE TABLE dbo.TBL_SAMPLE_DATA

(

Race CHAR(1) ...

serg-52 (11/14/2014)

---

VSP (11/13/2014)

---

Eirikur Eiriksson (11/12/2014)

---

Koen Verbeeck (11/12/2014)

---

In...

You are right on this, Luis's approach is better as it doesn't switch session contexts, just wanted to make the point that there are always alternatives.;-)

😎

How about this

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @PrVers NVARCHAR(128)

, @PrVersNum DECIMAL(10,2)

, @StageTable NVARCHAR(1024) = N''

SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

PRINT @PrVers

PRINT @PrVersNum...

Quick suggestion, not as elegant as Luis's but should work

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @PrVers NVARCHAR(128)

, @PrVersNum DECIMAL(10,2)

, @StageTable NVARCHAR(1024) = N''

SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

SELECT @PrVersNum = SUBSTRING(@PrVers,...

Hi and welcome to the forum. Just a quick note, in order to provide a sound advice on your query, some additional information is needed. Could you please provide the...

Just for quick fun, here is an alternative approach, should be easy to turn it into an iTVF (SQL Server 2012 and later)

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @CAP_TEXT VARCHAR(50) = 'UPPER...