January 5, 2009 at 9:28 pm
I really don't know MSSQL at all, so some seriously dumbed-down help is greatly appreciated...
In my table (itemsTable), I have records like this:
id | QId | SId | Items
1 | 3 | 8 | a,b,c,d,e,f,g
2 | 8 | 17 | b,g,c
3 | 6 | 19 | d,a,e,b
4 | 1 | 22 | f
5 | 9 | 67 | f,e
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
2 | b
2 | g
2 | c
etc. ?
First, (based on other sources I found on the web) I tried:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [mainDatabase]
GO
/****** Object: UserDefinedFunction [dbo].[GetListFromCSVString] Script Date: 01/05/2009 19:03:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [dpsumasternv]
GO
/****** Object: UserDefinedFunction [dbo].[GetListFromCSVString] Script Date: 01/05/2009 20:06:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetListFromCSVString]
(
@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](Items) as A
But got the "Items not recognized as table hints" message. I changed (Items) to [Items] and got "Incorrect syntax near the keyword as" error.
I also tried the code below, but it didn't separate the values. I still have rows like this:
4070365|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?
January 6, 2009 at 7:27 am
What compatibility mode is your database in?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2009 at 1:13 pm
I would think, if the database were in 80, that "CROSS APPLY" would get an error message instead of the thing about table hints. I don't have access to an SQL 2000 server to test that on right now, but I think it would work that way.
Edit: Nope. I'm wrong. Just set a test database to compatibility 80 and tried to cross apply a function, and got exactly "... is not a recognized table hint option...."
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 1:38 pm
GSquared (1/6/2009)
Edit: Nope. I'm wrong. Just set a test database to compatibility 80 and tried to cross apply a function, and got exactly "... is not a recognized table hint option...."
Yeah, it confused the hell out of me the first time I saw the error too. "Table hint????"
It's probably got to do with the order various bits of the query are parsed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply