parse string

Question

parse string

pnpsql

SSC-Addicted

Points: 456
More actions
May 15, 2012 at 9:31 am

#392264

i need to parse a string like
[p]a]a]s]s][p
when a [ sign occurs then next letter need to be convert in upper case and when a ]sign occurs the
next letter need to be convert in lower case.the sign [and ] need to be removed
the desired output of is : [p]a]a]s]s][p string is PaassP
please help
challenge everything

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

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

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

I'm not quite sure what you mean here. You say if a [ is next to the character then it needs to be uppercase, and if a ] is next to the character then it needs to be lowercase, but the first P has both a [ and a ] next to it so how do you determine that is supposed to be uppercase?

Edit: I actually read the OP. As stated below though, what needs to be done in the case of ][ showing up?

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 2

pnpsql (5/15/2012)
i need to parse a string like
[p]a]a]s]s][p
when a [ sign occurs then next letter need to be convert in upper case and when a ]sign occurs the
next letter need to be convert in lower case.the sign [and ] need to be removed
the desired output of is : [p]a]a]s]s][p string is PaassP
please help
challenge everything

A little more detail please as there are several ways to accomplish this.

Do you need the "parsed" results to be part of a SELECT statement? If so, we are probably looking at a function.

The example above shows a ][ together; what happens in that scenario?

Without writing out the entire solution for you could:

Read input string in groups of two characters (the [ or ] and the character)

If the first character is [ then subtract 32 (convert to upper) from the ASCII value of the second character

OR

If the first character is ] then add 32 (convert to upper) from the ASCII value of the second character

Append the resulted character based on the ASCII to a variable

Continue until the end of the string.

The above isn't bullet proof, doesn't check for input errors (such as non alpha characters, that an upper case is not being converted to an upper case) etc but it's a start.

It's nice to be provided solutions, but better to do a little research and accomplish the task yourself.

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

I would suggest that the looping concept above is not going to be very good for performance. What you are looking for is a slight tweak to an InitCap type of function.

The first step would be to make this look like a "sentence". In other words we want to replace all instances of ] with '' and all instances of [ with a space. This is pretty simple.

declare @Input varchar(50) = '[p]a]a]s]s][p'

select replace(REPLACE(@Input, ']', ''), '[', ' ')

The following thread has a great discussion about making the first character in a "word" be uppercase. You could follow this thread and look at opc.three's solution. It is much faster than a loop in t-sql. All you would need to do then is to remove the spaces from the output of that.

http://qa.sqlservercentral.com/Forums/Topic1298803-1292-1.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

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/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

drew.allen SSC Guru Points: 76776 More actions · Answer 4

It's "next" as in "following", not "next to" as in "adjacent". That still doesn't explain what to do with the "][", since the following character doesn't have an uppercase equivalent.

@OP, why are you trying to do this in T-SQL? T-SQL is fairly bad at text manipulation and there are scores of other approaches that would be much better.

For that matter, why is the text even coded in that method? As far as I know, every system in use today is capable of differentiating between uppercase and lowercase, so why would anyone be using an arcane system of encoding the difference when they could just use the desired case in the first place?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 5

This sort of things is straightforward as a SQLCLR, something like this

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text;

public partial class UserDefinedFunctions

{

enum States { Literal, UpperNext, LowerNext };

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString Parser(SqlString s)

{

if (s.IsNull) return SqlString.Null;

String src = s.Value;

StringBuilder sb = new StringBuilder(src.Length + 1);

States state = States.Literal;

foreach(char ch in src)

{

switch (ch)

{

case '[':

state = States.UpperNext;

break;

case ']':

state = States.LowerNext;

break;

default:

switch (state)

{

case States.Literal:

sb.Append(ch);

break;

case States.UpperNext:

sb.Append(Char.ToUpper(ch));

break;

case States.LowerNext:

sb.Append(Char.ToLower(ch));

break;

}

state = States.Literal;

break;

}

return new SqlString(sb.ToString());

}

};

Here's a setup for it

CREATE ASSEMBLY [SQLCLR]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030012CBB34F0000000000000000E00002210B010800000A000000060000000000000E280000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000BC2700004F000000004000007003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000001408000000200000000A000000020000000000000000000000000000200000602E72737263000000700300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000F0270000000000004800000002000500202100009C060000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200B9000000010000110F00280E00000A2C067E0F00000A2A0F00281000000A0A066F1100000A1758731200000A0B160C0613041613052B73110411056F1300000A0D09130611061F5B594503000000020000000A000000060000002B08170C2B44180C2B4008130711074503000000020000000C0000001B0000002B2607096F1400000A262B1C0709281500000A6F1400000A262B0D0709281600000A6F1400000A26160C110517581305110511046F1100000A3282076F1700000A731800000A2A1E02281900000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C0000005C020000237E0000C8020000E802000023537472696E677300000000B00500000800000023555300B8050000100000002347554944000000C8050000D400000023426C6F620000000000000002000001571D02000902000000FA01330016000001000000130000000300000004000000020000000100000019000000030000000B0000000100000001000000020000000100000000000A00010000000000060041003A00060048003A000A006E0059000600BB00A9000600F300D40006000701A90006002001A90006003B01A90006005601A90006006F01A90006008801A9000600A701A9000600C401A9000600FB01DB0106001B02DB010A005B02400206008C023A000600AA029E020600C9023A0000000000010000000000010001000100100015000000050001000100030100002A00000009000100030006068500150056808D00180056809500180056809F001800502000000000960078000A00010015210000000086187F001100020000000100700221007F002B0029007F00300031007F002B0039007F002B0041007F002B0049007F002B0051007F002B0059007F002B0061007F002B0069007F002B0071007F00350079007F00110081007F001100190072023A0019007D023E0019008202420089009302460091007F0035008900B8024A009100C2024F009900CE0255009900D60255000900DE02420019007F002B0009007F001100080008001C0008000C00210008001000260020006B0021002E003B009B002E00230068002E002B006E002E0033008F002E00130068002E00430068002E004B0068002E0053008F002E005B00AA002E006300B3005A000480000001000000A711896E00000000000039020000020000000000000000000000010031000000000002000000000000000000000001004D0000000000030002000000003C4D6F64756C653E0053514C434C522E646C6C0055736572446566696E656446756E6374696F6E7300537461746573006D73636F726C69620053797374656D004F626A65637400456E756D0053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E6700506172736572002E63746F720076616C75655F5F004C69746572616C0055707065724E657874004C6F7765724E6578740053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053514C434C52004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650073006765745F49734E756C6C004E756C6C006765745F56616C756500537472696E67006765745F4C656E6774680053797374656D2E5465787400537472696E674275696C646572006765745F436861727300417070656E64004368617200546F557070657200546F4C6F77657200546F537472696E6700000003200000000000C990D4F7FFD0BD499ECA2CCB9ADD37610008B77A5C561934E089060001110D110D032000010206080306110C040000000004010000000402000000042001010E04200101020420010108032000020306110D0320000E03200008042001030805200112490304000103030D07080E1249110C030E0803110C0501000000002001001B436F7079726967687420C2A9204D6963726F736F6674203230313200000B01000653514C434C5200000E0100094D6963726F736F667400000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000E42700000000000000000000FE270000002000000000000000000000000000000000000000000000F0270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000180300000000000000000000180334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100896EA71100000100896EA7113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00478020000010053007400720069006E006700460069006C00650049006E0066006F00000054020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000380007000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C0043004C0052000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003500310039002E00320038003200390037000000000038000B00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310032000000000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052002E0064006C006C0000000000300007000100500072006F0064007500630074004E0061006D00650000000000530051004C0043004C0052000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003500310039002E00320038003200390037000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003500310039002E003200380032003900370000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION [dbo].[Parser](@s [nvarchar](4000))

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [SQLCLR].[UserDefinedFunctions].[Parser]

GO

SELECT dbo.Parser('[p]a]a]s]s][p')

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

Do you like puzzles?

declare @in varchar(50)

declare @out varchar(50)

set @in = '[p]a]a]s]s][p'

select @in as [IN],

(

select r+''

from

(

select case when a = '[' then UPPER(b) else LOWER(b) end r

from

(

select SUBSTRING(REPLACE(REPLACE(@in,'][','['),'[]',']'),rn-1,1) a

,SUBSTRING(REPLACE(REPLACE(@in,'][','['),'[]',']'),rn,1) b

from (select top (len(REPLACE(REPLACE(@in,'][','['),'[]',']')))

ROW_NUMBER() over (ORDER BY (SELECT NULL)) rn FROM sys.columns) q

) s

where a in ('[',']')

) f for xml path('')

) as [OUT]

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 7

I think this last group of posts shows that there are several ways to attack this (including my inefficient loop).

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

Dwain Camps SSC Guru Points: 86913 More actions · Answer 8

Another puzzle:

DECLARE @t TABLE (ID INT IDENTITY, strcol VARCHAR(100))

INSERT INTO @t

SELECT '[p]a]a]s]s][p'

UNION ALL SELECT '[z]z]z]z]y][y'

;WITH Tally (n) AS (

SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)

SELECT ID, strcol

,REPLACE(REPLACE((

SELECT delim

FROM (

SELECT ID, n as ColumnInString

,CASE WHEN SUBSTRING(strcol, n, 1) = '[' THEN UPPER(SUBSTRING(strcol, n + 1, 1))

ELSE LOWER(SUBSTRING(strcol, n + 1, 1)) END AS delim

,strcol

FROM @t

CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND LEN(strcol)) x

WHERE SUBSTRING(strcol, n, 1) IN ('[',']')) t2

WHERE t1.ID = t2.ID

FOR XML PATH(''), root('M'), type).value('/M[1]','varchar(max)' )

, '[', ''), ']', '') As ParsedString

FROM @t t1

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

pnpsql SSC-Addicted Points: 456 More actions · Answer 9

thanks to all , now i do it my way can you please help to shorten this code

declare@STRvarchar(20)= '[p]a]a]s]s][p'

declare@posint

select@pos= charindex('[', @STR)

while@pos<> 0

begin

select@STR = stuff(@str, @pos + 1, 1, upper(substring(@str, @pos + 1, 1)))

select@pos= charindex('[', @STR, @pos + 1)

end

selectreplace(replace(@str, '[', ''), ']', '')

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

pnpsql (5/15/2012)
thanks to all , now i do it my way can you please help to shorten this code
declare@STRvarchar(20)= '[p]a]a]s]s][p'
declare@posint
select@pos= charindex('[', @STR)
while@pos<> 0
begin
select@STR = stuff(@str, @pos + 1, 1, upper(substring(@str, @pos + 1, 1)))
select@pos= charindex('[', @STR, @pos + 1)
end
selectreplace(replace(@str, '[', ''), ']', '')

Nicely done and "Yes". It can be shortened a bit and the changes reduce the number of internal row counts generated by 28% making the code about another 0 to 4% faster (depending on your machine) than what you've already been able to attain. I've included the code below as a Scalar Function without much loss of performance just to make it convenient to run. Either way smokes all but the CLR solution offered so far. I don't know about the CLR solution because I don't have the environment setup to compile it.

CREATE FUNCTION dbo.ParseBrace

(@pString VARCHAR(8000))

RETURNS VARCHAR(8000) WITH SCHEMABINDING AS

BEGIN

DECLARE @Posit INT;

SELECT @pString = LOWER(REPLACE(@pString,']','')),

@Posit = PATINDEX('%[[][a-z]%',@pString);

WHILE @Posit > 0

SELECT @pString = STUFF(@pString,@Posit,2,UPPER(SUBSTRING(@pString,@Posit+1,1))),

@Posit = PATINDEX('%[[][a-z]%',@pString);

RETURN @pString;

END;

Now, if you'll excuse me, I heard a couple of jaws drop and I have to help a couple of folks find their dentures. 😛

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Sean Lange (5/15/2012)
I would suggest that the looping concept above is not going to be very good for performance.

Actually, that's not true for these types of problems. About the only thing that will beat a While Loop for these types of "memory only" solutions is a CLR. See the code I posted above (or the code the OP posted above that) and try to beat it with anything other than a CLR.

--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)
Intro to Tally Tables and Functions

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 12

Jeff Moden (5/15/2012)
pnpsql (5/15/2012)
thanks to all , now i do it my way can you please help to shorten this code
declare@STRvarchar(20)= '[p]a]a]s]s][p'
declare@posint
select@pos= charindex('[', @STR)
while@pos<> 0
begin
select@STR = stuff(@str, @pos + 1, 1, upper(substring(@str, @pos + 1, 1)))
select@pos= charindex('[', @STR, @pos + 1)
end
selectreplace(replace(@str, '[', ''), ']', '')
Nicely done and "Yes". It can be shortened a bit and the changes reduce the number of internal row counts generated by 28% making the code about another 0 to 4% faster (depending on your machine) than what you've already been able to attain. I've included the code below as a Scalar Function without much loss of performance just to make it convenient to run. Either way smokes all but the CLR solution offered so far. I don't know about the CLR solution because I don't have the environment setup to compile it.
CREATE FUNCTION dbo.ParseBrace
(@pString VARCHAR(8000))
RETURNS VARCHAR(8000) WITH SCHEMABINDING AS
BEGIN
DECLARE @Posit INT;
SELECT @pString = LOWER(REPLACE(@pString,']','')),
@Posit = PATINDEX('%[[][a-z]%',@pString);
WHILE @Posit > 0
SELECT @pString = STUFF(@pString,@Posit,2,UPPER(SUBSTRING(@pString,@Posit+1,1))),
@Posit = PATINDEX('%[[][a-z]%',@pString);
RETURN @pString;
END;
Now, if you'll excuse me, I heard a couple of jaws drop and I have to help a couple of folks find their dentures. 😛

OMG :w00t:

Jeff Moden posted a loop.

There is only darkness now, there is no sunshine. The mayans were correct.

Parden me, I must send out an email blast to our IT department 😀

Please don't step on my dentures.

Converting oxygen into carbon dioxide, since 1955.

Sean Lange SSC Guru Points: 286562 More actions · Answer 13

OMG :w00t:
Jeff Moden posted a loop.
There is only darkness now, there is no sunshine. The mayans were correct.
Parden me, I must send out an email blast to our IT department 😀
Please don't step on my dentures.

+1 - ROFL

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

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/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286562 More actions · Answer 14

Jeff Moden (5/15/2012)
Sean Lange (5/15/2012)
I would suggest that the looping concept above is not going to be very good for performance.
Actually, that's not true for these types of problems. About the only thing that will beat a While Loop for these types of "memory only" solutions is a CLR. See the code I posted above (or the code the OP posted above that) and try to beat it with anything other than a CLR.

Now you have me curious Jeff. I am wrapping up stuff at work today and out the rest of the week. If I can remember this next week I will toss some stuff at it just out of curiosity. I have a feeling I will be flogging a dead horse but it is worth a shot.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

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/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/