nickgross19
asked on
SQL question
I am using MS SQL Server 2008 and I need to create a view from values in a table.
The table the view will pull its data from has a column that contains values which are separated by commas. For example the information in one field in this column might be: 123kdkd, 2939439
I want to take each value that is in the field and put it into one field in the view. So if I were to do this for the sample data I provided, my view would look like this
ID Value
----------- | -----------
1 | 123kdkd
2 | 2939439
I think I might need to use the charindex() and substring() functions and do a loop, but I am new to T-SQL and haven't done a loop in SQL before.
The table the view will pull its data from has a column that contains values which are separated by commas. For example the information in one field in this column might be: 123kdkd, 2939439
I want to take each value that is in the field and put it into one field in the view. So if I were to do this for the sample data I provided, my view would look like this
ID Value
----------- | -----------
1 | 123kdkd
2 | 2939439
I think I might need to use the charindex() and substring() functions and do a loop, but I am new to T-SQL and haven't done a loop in SQL before.
Last Comment
If just two, then do this:
select ID, select SUBSTRING(Value,0,charinde
select ID, select SUBSTRING(Value,0,charinde
... from yourTable
oops, messed up the select, it should be (again, if there are always only two values in the string)
select ID, SUBSTRING(Value,0,charinde
from yourTable
select ID, SUBSTRING(Value,0,charinde
from yourTable
and to make a view out of it:
create view myView as
select ID, SUBSTRING(Value,0,charinde
from yourTable
GO
select * from myView
create view myView as
select ID, SUBSTRING(Value,0,charinde
from yourTable
GO
select * from myView
ASKER
There are usually just two, but to be safe I want to take into account the possibility that there could be an unknown number more.
ASKER
Consequently, I can't just do 3 or 4 replace functions because there could be more or less values than that!
In that case you will need a function to split the CSV string. First create this function:
create FUNCTION [dbo].[csv1] (@Source VARCHAR(MAX), @rowDelim VARCHAR(max)=',')
RETURNS @Result TABLE (ID int identity, col1 varchar(MAX))
WITH SCHEMABINDING
AS
BEGIN
IF isNull(@rowDelim,'') in (char(10),'')
BEGIN
SELECT @rowDelim = char(13)+char(10)
END
ELSE
BEGIN
SELECT @Source = replace( @Source, @rowDelim+char(13), @rowDelim )
SELECT @Source = replace( @Source, @rowDelim+char(10), @rowDelim )
END
SELECT @Source = rtrim(ltrim(@Source))
SELECT @Source = @Source+@rowDelim WHERE RIGHT(@Source,1) != @rowDelim
DECLARE @rowDelimPos int=0, @StartShift int=1, @LenSource int=len(@Source)
WHILE @StartShift <= @LenSource
BEGIN
SELECT @rowDelimPos = charindex( @rowDelim, @Source, @StartShift )
INSERT INTO @Result VALUES ( substring(@Source,@StartShift,@rowDelimPos-@StartShift) )
SELECT @StartShift = @rowDelimPos + len(@rowDelim)
END
RETURN
END
... stand by, i'll be posting how to use it momentarily..
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
okay, i hit some strangeness and it took me a minute to figure it out.
But before that you must realize that it is not possible to create a view with a variable number of columns. Therefore you must choose a finite number -- in this case we'll use 4.
I use the function from the previous post along with outer apply to pull in the extra columns, but hit a snag when some of the Values had more commas than others. So I employed a trick and appended extra commas on the Value column in each outer apply. This seems to do the trick... See below:
However, this won't be a very efficient query -- it will probably run slow against very large data sets.
But before that you must realize that it is not possible to create a view with a variable number of columns. Therefore you must choose a finite number -- in this case we'll use 4.
I use the function from the previous post along with outer apply to pull in the extra columns, but hit a snag when some of the Values had more commas than others. So I employed a trick and appended extra commas on the Value column in each outer apply. This seems to do the trick... See below:
However, this won't be a very efficient query -- it will probably run slow against very large data sets.
create view MyView as
select T.ID, F1.col1 as VC1, F2.col1 as VC2, F3.col1 as VC3, F4.col1 as VC4
from MyTable T
outer apply dbo.csv1( T.Value+',,,,', ',' ) F1
outer apply dbo.csv1( T.Value+',,,,', ',' ) F2
outer apply dbo.csv1( T.Value+',,,,', ',' ) F3
outer apply dbo.csv1( T.Value+',,,,', ',' ) F4
where F1.ID=1
and F2.ID=2
and F3.ID=3
and F4.ID=4
btw - I just noticed that your sample table has the exact columns - ID and Value - as the function. This is just a coincidence. I mention it only because it may cause some confusion, so note the table qualifiers in the post above to keep it straight. :)
Also note that I appended 4 commas ',,,,' to Value in the outer apply clauses -- this is to match the 4 columns in the view.
As it is now, rows with NULL for Value (in your table) will be excluded from the results. Let me know if you want to include them (all the VC# columns will be null for rows like this).
As it is now, rows with NULL for Value (in your table) will be excluded from the results. Let me know if you want to include them (all the VC# columns will be null for rows like this).
Note that the view suggested above will handle different Values with varying numbers of commas (up to 4), so it will still work even if your table looks like this:
1 123kdkd,2939439
2 123kdkd,2939439,xxxyyyzzz,
3 xyzpdq,yyz,123
1 123kdkd,2939439
2 123kdkd,2939439,xxxyyyzzz,
3 xyzpdq,yyz,123
correction - up to 3 commas representing 4 columns
>>But before that you must realize that it is not possible to create a view with a variable number of columns. <<
But that is just the point, it only has two: ID and Value. Which is a lot simpler and makes it a perfect candidate for CROSS APPLY as JestersGrind showed here http:#a36933430
But that is just the point, it only has two: ID and Value. Which is a lot simpler and makes it a perfect candidate for CROSS APPLY as JestersGrind showed here http:#a36933430
I believe he was asking for a separate column in the view for each value between commas in the Value column, of which there could be 1 to n. The view itself cannot have 3 columns with one query and 5 with the next based on how many commas are in Value.
The author's output in the original question only has two columns: ID and Value. If this has now changed they need to clarify.
He did so here: http:#36933371
>> There are usually just two, but to be safe I want to take into account the possibility that there could be an unknown number more. <<
...which led to my reply:
>> But before that you must realize that it is not possible to create a view with a variable number of columns. <<
>> There are usually just two, but to be safe I want to take into account the possibility that there could be an unknown number more. <<
...which led to my reply:
>> But before that you must realize that it is not possible to create a view with a variable number of columns. <<
ASKER
In my original question I only needed two columns. The first column would contain the ID and the other column would contain the part number. SO the two columns would be a candidate key; thus there could be many rows with the same value for ID; but each row with the same ID would have a different part number with it.
exactly, and several early proposals would work for a two-column solution when Value contains just one comma.
But then you asked for a multi-column solution for when Value contained multiple commas (e.g. columns), which sparked the later proposals.
My reply about the view was addressing this.
But then you asked for a multi-column solution for when Value contained multiple commas (e.g. columns), which sparked the later proposals.
My reply about the view was addressing this.
By splitting the string and reducing the horizontal, comma delimited string to a vertical, two column table, it doesn't matter how many elements are in the original string. You just end up getting a row back for each element in the string.
Greg
Greg
Yes, both functions above do this. The trick is to make it work when there is more than one row of Values containing more than one comma -- especially if Value in one row contains x commas and in another row it contains y commas.
I am so confused...
I am glad you guys understand the authors requirements.
I am glad you guys understand the authors requirements.
ASKER
This code from what I can tell shows the record ID and each value that was located in the column with values delimited by commas.
Thanks!
Thanks!
Microsoft SQL Server 2008
Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.
50K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Or can there be more than one? 123kdkd,2939439,xxxyyyzzz,