jen_jen_jen
asked on
MSSQL - Way to explode field values on queryout?
I'm assuming this is impossible, but thought I'd ask anyway....
In my table, I'll have records like this:
1 | a,b,c,d,e,f,g
2 | a,b,c
3 | a,d,e,g
4 | f
5 | e,f
I want to export the results to a text file. Is it possible to explode those values so that I wind up with:
1 | a
1 | b
1 | c
1 | d
1 | e
1 | f
1 | g
etc. ?
In my table, I'll have records like this:
1 | a,b,c,d,e,f,g
2 | a,b,c
3 | a,d,e,g
4 | f
5 | e,f
I want to export the results to a text file. Is it possible to explode those values so that I wind up with:
1 | a
1 | b
1 | c
1 | d
1 | e
1 | f
1 | g
etc. ?
ASKER
I'm not at all familiar with MSSQL, so "use a view to expand them out", doesn't ring clear with me? Can you explain that?
And, by fixed values, do you mean the same values, or the same values in the same order? They may not be in the same order each time...
And, by fixed values, do you mean the same values, or the same values in the same order? They may not be in the same order each time...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here you go:
BEGIN
--1 | a,b,c,d,e,f,g
--2 | a,b,c
--3 | a,d,e,g
--4 | f
--5 | e,f
CREATE TABLE #t (id int
,itemval char(1))
INSERT #t (id
,itemval)
SELECT 1
,'a'
UNION ALL
SELECT 1
,'b'
UNION ALL
SELECT 1
,'c'
UNION ALL
SELECT 1
,'d'
UNION ALL
SELECT 1
,'e'
UNION ALL
SELECT 1
,'f'
UNION ALL
SELECT 1
,'g'
UNION ALL
SELECT 2
,'a'
UNION ALL
SELECT 2
,'b'
UNION ALL
SELECT 2
,'c'
UNION ALL
SELECT 3
,'a'
UNION ALL
SELECT 3
,'d'
UNION ALL
SELECT 3
,'e'
UNION ALL
SELECT 3
,'g'
UNION ALL
SELECT 4
,'f'
UNION ALL
SELECT 5
,'e'
UNION ALL
SELECT 5
,'f'
SELECT DISTINCT t.id
,t.itemval
FROM #t t
,(SELECT t2.id
,t2.itemval
FROM #t t2) as tt
WHERE t.id = tt.id
ORDER BY t.id ASC
END
GO
DROP TABLE #t
GOASKER
Hmm, ok, do I have to specify all of the various combinations in that select statement? Can I just do:
declare @t table(id int, other varchar(15))
insert into @t
Select id, other
or something like that?
Because there's more than 60 million rows in this table...
declare @t table(id int, other varchar(15))
insert into @t
Select id, other
or something like that?
Because there's more than 60 million rows in this table...
appari's code is good, too. The only fundamental difference is that appari uses cross apply, in which there are performance implications.
Effectively, if you have the text data in a table already, you'll only need:
Effectively, if you have the text data in a table already, you'll only need:
SELECT DISTINCT t.id
,t.itemval
FROM #t t
,(SELECT t2.id
,t2.itemval
FROM #t t2) as tt
WHERE t.id = tt.id
ORDER BY t.id ASC
Above, where #t is your table name
ASKER
Ok, so first I need to create a table of all possible values? Then I can use that last select statement you posted?
jen_jen_jen,
You can create a DTS dump to import your text data into a SQL table.
In SQL Server Managment Studio 2005, right click on a database, click Import/Export and select the source (text file) and provide a destination (SQL table).
Instructions on how to do this:
http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm
Then you can run the select statement:
You can create a DTS dump to import your text data into a SQL table.
In SQL Server Managment Studio 2005, right click on a database, click Import/Export and select the source (text file) and provide a destination (SQL table).
Instructions on how to do this:
http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm
Then you can run the select statement:
SELECT DISTINCT t.id
,t.itemval
FROM your_destination_table_name t
,(SELECT t2.id
,t2.itemval
FROM your_destination_table_name t2) as tt
WHERE t.id = tt.id
ORDER BY t.id ASC
ASKER
Sorry.... this confuses me. What text data are you referring to? The data that already exists in a table? Why would I export that and then import it again?
Id | Values
1 | a,b,c,d
2 | b,a,d
3 | b
4 | d, a, c
etc.?
Id | Values
1 | a,b,c,d
2 | b,a,d
3 | b
4 | d, a, c
etc.?
Ok, if the values are already in a table (i.e., you've already imported the values into a SQL table), you can just use the statement:
SELECT DISTINCT t.id
,t.itemval
FROM your_destination_table_nam
,(SELECT t2.id
,t2.itemval
FROM your_destination_table_nam
WHERE t.id = tt.id
ORDER BY t.id ASC
You just need to change the above "your_destination_table_na
SELECT DISTINCT t.id
,t.itemval
FROM your_destination_table_nam
,(SELECT t2.id
,t2.itemval
FROM your_destination_table_nam
WHERE t.id = tt.id
ORDER BY t.id ASC
You just need to change the above "your_destination_table_na
Because there's more than 60 million rows in this table...
if you have data already existing in a table you dont need to insert in to another table. i used @t to give you the sample code. create the function and use the following sql
Select yourTableName.*, A.* from yourTableName
cross apply dbo.[GetListFromCSVString]
if you have data already existing in a table you dont need to insert in to another table. i used @t to give you the sample code. create the function and use the following sql
Select yourTableName.*, A.* from yourTableName
cross apply dbo.[GetListFromCSVString]
ASKER
Ok, I tried the function and the select, but got "Items" is not a recognized table hints option.
Specifically, the table looks like this:
Id | QId | SId | PId | Items
1 | 23 | 5 | 8 | A, B, C, E, F
2 | 23 | 5 | 2 | F, B, C, A, E
3 | 78 | 12 | 9 | B, C, A, E
Etc.
I tried this select statement:
SELECT Id, QId, A.* FROM itemsTable
CROSS APPLY dbo.[GetListFromCSVString]
But got that error?
Specifically, the table looks like this:
Id | QId | SId | PId | Items
1 | 23 | 5 | 8 | A, B, C, E, F
2 | 23 | 5 | 2 | F, B, C, A, E
3 | 78 | 12 | 9 | B, C, A, E
Etc.
I tried this select statement:
SELECT Id, QId, A.* FROM itemsTable
CROSS APPLY dbo.[GetListFromCSVString]
But got that error?
ASKER
I've tried both of these suggestions and can't get either one to work.
First, I tried:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [mainDatabase]
GO
/****** Object: UserDefinedFunction [dbo].[GetListFromCSVStrin
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [dpsumasternv]
GO
/****** Object: UserDefinedFunction [dbo].[GetListFromCSVStrin
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetListFromCSVStrin
(
@csvString varchar(500)
)
RETURNS @ValueList TABLE
(
ListValue VARCHAR(50)
)
AS
BEGIN
DECLARE @curPos int
DECLARE @prevCurPos int
SET @curPos = -1
SET @prevCurPos = 1
WHILE @curPos <>0
BEGIN
SET @curPos = charindex(',',@csvString, @curPos + 1)
IF @curPos <> 0
BEGIN
INSERT INTO @ValueList values (substring(@csvString, @prevCurPos, @curPos - @prevCurPos))
END
ELSE
BEGIN
INSERT INTO @ValueList values (substring(@csvString, @prevCurPos, len(@csvString) - @prevCurPos + 1))
END
SET @prevCurPos = @curPos + 1
END
RETURN
END
And I called it like this:
SELECT Id, QId, A.* FROM itemsTable
CROSS APPLY dbo.[GetListFromCSVString]
But got the not recognized as table hints message. I changed (Items) to [Items] and got "Incorrect syntax near the keyword as" error.
I also tried Scotty's suggestion, but it didn't separate the values. I still have rows like this:
4070365|59|B,A,D,F,G
I used:
SELECT DISTINCT t.id
,t.Items
FROM itemsTable t
,(SELECT t2.id
,t2.Items
FROM itemsTable t2) as tt
WHERE t.id = tt.id
ORDER BY t.id ASC
But, like I said, I'm still getting rows that have values separated by commas... What am I misunderstanding?
First, I tried:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [mainDatabase]
GO
/****** Object: UserDefinedFunction [dbo].[GetListFromCSVStrin
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [dpsumasternv]
GO
/****** Object: UserDefinedFunction [dbo].[GetListFromCSVStrin
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetListFromCSVStrin
(
@csvString varchar(500)
)
RETURNS @ValueList TABLE
(
ListValue VARCHAR(50)
)
AS
BEGIN
DECLARE @curPos int
DECLARE @prevCurPos int
SET @curPos = -1
SET @prevCurPos = 1
WHILE @curPos <>0
BEGIN
SET @curPos = charindex(',',@csvString, @curPos + 1)
IF @curPos <> 0
BEGIN
INSERT INTO @ValueList values (substring(@csvString, @prevCurPos, @curPos - @prevCurPos))
END
ELSE
BEGIN
INSERT INTO @ValueList values (substring(@csvString, @prevCurPos, len(@csvString) - @prevCurPos + 1))
END
SET @prevCurPos = @curPos + 1
END
RETURN
END
And I called it like this:
SELECT Id, QId, A.* FROM itemsTable
CROSS APPLY dbo.[GetListFromCSVString]
But got the not recognized as table hints message. I changed (Items) to [Items] and got "Incorrect syntax near the keyword as" error.
I also tried Scotty's suggestion, but it didn't separate the values. I still have rows like this:
4070365|59|B,A,D,F,G
I used:
SELECT DISTINCT t.id
,t.Items
FROM itemsTable t
,(SELECT t2.id
,t2.Items
FROM itemsTable t2) as tt
WHERE t.id = tt.id
ORDER BY t.id ASC
But, like I said, I'm still getting rows that have values separated by commas... What am I misunderstanding?
can you execute the following and see if the CSV is splitted properly or not.
and what version of the sql are you using?
select * from dbo.[GetListFromCSVString]
and what version of the sql are you using?
select * from dbo.[GetListFromCSVString]
and if you are using sql 2005 or 2008 what is the database compatibilty level?
http://blogs.msdn.com/psssql/archive/2007/10/16/database-compatibility-and-new-features.aspx
http://blogs.msdn.com/psssql/archive/2007/10/16/database-compatibility-and-new-features.aspx
jen_jen_jen,
You stated,
"Ok, I tried the function and the select, but got "Items" is not a recognized table hints option.
Specifically, the table looks like this:
Id | QId | SId | PId | Items
1 | 23 | 5 | 8 | A, B, C, E, F
2 | 23 | 5 | 2 | F, B, C, A, E
3 | 78 | 12 | 9 | B, C, A, E"
I this what your structure looks like? If so, what kind of results would you like? The possible solutions myself and appari gave were solutions for a different format:
1 | a,b,c,d,e,f,g
2 | a,b,c
3 | a,d,e,g
4 | f
5 | e,f
And regretfully, this is why our solutions are not solving the issue. What is your final format?
You stated,
"Ok, I tried the function and the select, but got "Items" is not a recognized table hints option.
Specifically, the table looks like this:
Id | QId | SId | PId | Items
1 | 23 | 5 | 8 | A, B, C, E, F
2 | 23 | 5 | 2 | F, B, C, A, E
3 | 78 | 12 | 9 | B, C, A, E"
I this what your structure looks like? If so, what kind of results would you like? The possible solutions myself and appari gave were solutions for a different format:
1 | a,b,c,d,e,f,g
2 | a,b,c
3 | a,d,e,g
4 | f
5 | e,f
And regretfully, this is why our solutions are not solving the issue. What is your final format?
ASKER
A million apologies. I tried to simplify things, but I see now that that was a dumb idea.... The table schema does in fact look like this:
Id | QId | SId | PId | Items
1 | 23 | 5 | 8 | A, B, C, E, F
2 | 23 | 5 | 2 | F, B, C, A, E
3 | 78 | 12 | 9 | B, C, A, E
And I want to pull Id, QId, and Items (separated by comma) from it.
Id | QId | SId | PId | Items
1 | 23 | 5 | 8 | A, B, C, E, F
2 | 23 | 5 | 2 | F, B, C, A, E
3 | 78 | 12 | 9 | B, C, A, E
And I want to pull Id, QId, and Items (separated by comma) from it.
>>A million apologies. I tried to simplify things, but I see now that that was a dumb idea.... The table schema does in fact look like this:
thats nothing to do with the extra fields,
>>Ok, I tried the function and the select, but got "Items" is not a recognized table hints option.
you are getting this error means most likely its something related to db compatibility level,
what is your database version? if it is sql 2005 or above what is the db compatibility level?
and execute the following and post if you are getting the proper results(csv spliited to rows) or not
select * from dbo.[GetListFromCSVString]
thats nothing to do with the extra fields,
>>Ok, I tried the function and the select, but got "Items" is not a recognized table hints option.
you are getting this error means most likely its something related to db compatibility level,
what is your database version? if it is sql 2005 or above what is the db compatibility level?
and execute the following and post if you are getting the proper results(csv spliited to rows) or not
select * from dbo.[GetListFromCSVString]
ASKER
Ok, I ran the above command, and it did properly split the strings. Then I tried running:
SELECT * FROM dbo.itemsTable
CROSS APPLY dbo.[GetListFromCSVString]
and got
'Items' is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.
SELECT * FROM dbo.itemsTable
CROSS APPLY dbo.[GetListFromCSVString]
and got
'Items' is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.
ASKER
Ok, I change the compatibility level to 90, and the query starts to run, but then terminates with the error message: String or binary data would be truncated
ASKER
Ok, I changed the VARCHAR in the function to match the csvstring in the table, and it's working. Thank you very much for helping me do this. I know I've been frustrating... :)
i.e. can you load these values into another static table? Then you can use a view to expand them out.
If they are not fixed known values then you'll need to use an stored procedure to dump the data to a temp table and select it out.
There's probably a way in SSIS to do this also.