SQL Syntax for Splitting a Column Up and then Using Split Columns in Condition

DECLARE @S varchar(max),
  @Split char(1),
  @X xml

SELECT @S = '1972,1973,1974,1975',
  @Split = ','

SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c)

BCUNNEY,

This returns the individual years as separate rows, but now separate columns which is what I need.  Basically I have each row representing a project and I want to have the name of the project in one column and then each of the years that the project was funded out of in columns out from the project name like:

ABC Project              1972                1973                  1974
XYZ Project               2000                2001    
LMN Project             1999
STU Project               1983                1984                 1985           1986

the below will work for up to ten years, you can keep adding columns there or go with dynamic SQL:

select projectname, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]

from (
	select 	projectname, 
		SUBSTRING(RowData, n, CHARINDEX(@spliton, RowData + @spliton,n) - n) AS y, 
		row_number() over (partition by projectname order by SUBSTRING(RowData, n, CHARINDEX(@spliton, RowData + @spliton,n) - n)) rn
	from yourtable
	cross join (SELECT number FROM master..spt_values WHERE type = 'P' and number >0) AS Numbers(n) 
	where substring(',' + Yearcolumn, n, 1) = ','
	and n < len(YearColumn) + 1
) o
pivot (max(y) for rn in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) p

Select allOpen in new window

Get two errors:

Must declare the scalar variable "@spliton"

and

Incorrect syntax near the word 'AS'

oops

select projectname, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]

from (
	select 	projectname, 
		SUBSTRING(RowData, n, CHARINDEX(',', RowData + ',',n) - n) AS y, 
		row_number() over (partition by projectname order by SUBSTRING(RowData, n, CHARINDEX(',', RowData + ',',n) - n)) rn
	from yourtable
	cross join (SELECT number FROM master..spt_values WHERE type = 'P' and number >0) AS Numbers(n) 
	where substring(',' + Yearcolumn, n, 1) = ','
	and n < len(YearColumn) + 1
) o
pivot (max(y) for rn in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) p

Select allOpen in new window

and actually you can just do

select projectname, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]

from (
	select 	projectname, 
		SUBSTRING(RowData, n, CHARINDEX(',', RowData + ',',n) - n) AS y, 
		n
	from yourtable
	cross join (SELECT number FROM master..spt_values WHERE type = 'P' and number >0) AS Numbers(n) 
	where substring(',' + Yearcolumn, n, 1) = ','
	and n < len(YearColumn) + 1
) o
pivot (max(y) for n in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) p

Select allOpen in new window

Now I get this:

Incorrect syntax near 'pivot'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

ok, so are you in SQL 2008? That error is because you're running SQL 2000 or your database compatibility level is set to 80. Can you change it?

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 90;

http://msdn.microsoft.com/en-us/library/bb510680.aspx

I was able to run the Alter Database script that you sent it was successful, but now I get these errors:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'RowData'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'RowData'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'projectname'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'RowData'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'RowData'.

rowdata should be your column name, likewise with projectname, I don't know your column names so I'm just assuming

select projectname, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]

from (
      select       projectname,
            SUBSTRING(YearColumn, n, CHARINDEX(',', YearColumn + ',',n) - n) AS y,
            n
      from yourtable
      cross join (SELECT number FROM master..spt_values WHERE type = 'P' and number >0) AS Numbers(n)
      where substring(',' + Yearcolumn, n, 1) = ','
      and n < len(YearColumn) + 1
) o
pivot (max(y) for n in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) p

Not quite working.  I get a number in the [1] column and then all the rest of years only appear in the [6] column but I am not getting multiple years beyond 2 years.  I provided a image of just a portion of the data so you can see what is happening.
Capture.JPG

can you run this and post what you get?

   

   select       projectname, 
            SUBSTRING(YearColumn, n, CHARINDEX(',', YearColumn + ',',n) - n) AS y, 
            n
      from yourtable
      cross join (SELECT number FROM master..spt_values WHERE type = 'P' and number >0) AS Numbers(n) 
      where substring(',' + Yearcolumn, n, 1) = ','
      and n < len(YearColumn) + 1

Select allOpen in new window

It returns three columns, the project name and a column y and n.  The y column returns one year and then the n column returns a number from 1 to 24.
Capture.JPG

Ok, so let's try this then

select projectname, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
from (
	select 	projectname, 
		SUBSTRING(YearColumn, n, CHARINDEX(',', YearColumn + ',',n) - n) AS y, 
		row_number() over (partition by projectname order by SUBSTRING(YearColumn, n, CHARINDEX(',', YearColumn + ',',n) - n)) rn

	from yourtable
	cross join (SELECT number FROM master..spt_values WHERE type = 'P' and number >0) AS Numbers(n) 
	where substring(',' + Yearcolumn, n, 1) = ','
	and n < len(YearColumn) + 1
) o
pivot (max(y) for rn in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) p

Select allOpen in new window

I use a "split" function to do this:

CREATE FUNCTION [dbo].[Split](
    @sInputList VARCHAR(MAX) -- List of delimited items
  , @sDelimiter CHAR(1) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(MAX))

BEGIN
DECLARE @sItem VARCHAR(MAX)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

Select allOpen in new window

I created this function in my "master" db and I use it this way:

select * from master.dbo.Split('1972,1973,1974,1975', ',')

Select allOpen in new window

It will return:
1972
1973
1974
1975

May this help for your query?

Ralmada,

It looks like this is almost working.  The only thing that I am wondering is whether it is possible to put the years in columns in ascending order.  Meaning the first column is the oldest year and whatever is the final column to the right is the youngest year.  Right now they are in some random order like in the image.
Capture.JPG

try casting to integer there, so change this column to:

		row_number() over (partition by projectname order by cast(SUBSTRING(YearColumn, n, CHARINDEX(',', YearColumn + ',',n) - n) as integer)) rn

Select allOpen in new window

Conversion failed when converting the varchar value '1994/1996' to data type int.

Those values were not splitted because the delimiter was not ',' but '/'

Thank you very much for sticking with me on this and helping to deal with each little hassle I was having.  This works exactly like I would like it to.  I greatly appreciate your help.