Can this be done set-based or must I use a cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Can this be done set-based or must I use a cursor?

Author

Topic

hamble18
Starting Member

12 Posts

Posted - 2008-08-15 : 05:29:16

I have a series of responses that people have made in a survey, and they are stored in a table as follows:

PersonID,QuestionID,ResponsesGiven
1,1,'01.02.05'
1,2,'02.04.06.'
2,1,'01.03.'

As you can see, the responses are stored in a string. Each response is always 3 chars long (though not necessarily of the format 'nn.'). I can't change the structure of this table. However, I want to write an insert/update trigger on this table to generate a second table which stores it as the following, which will be much easier to report on:

PersonID,QuestionID,Response
1,1,'01.'
1,1,'02.'
1,1,'05.'
1,2,'02.'
1,2,'04.'
1,2,'06.'
2,1,'01.'
2,1,'03.'

Anyone know if it is possible to break the string into multiple records without looping row by row?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-15 : 05:35:31

make use of CSVTable or fnParseList and CROSS APPLY to it
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 07:16:13

Always three characters?

DECLARE	@Sample TABLE
	(
		PersonID INT,
		QuestionID INT,
		ResponsesGiven VARCHAR(1000)
	)

INSERT	@Sample
SELECT	1, 1, '01.02.05.' UNION ALL
SELECT	1, 2, '02.04.06.' UNION ALL
SELECT	2, 1, '01.03.'

SELECT		s.PersonID,
		s.QuestionID,
		SUBSTRING(s.ResponsesGiven, 3 * v.Number + 1, 3) AS ResponsesGiven
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number < LEN(s.ResponsesGiven) / 3

E 12°55'05.25"
N 56°04'39.16"

hamble18
Starting Member

12 Posts

Posted - 2008-08-15 : 11:02:05

Peso

Thank you very much once again. I've come from a procedural coding background (FoxPro) and at first sight SELECT seems so limiting, but tricks like this using number tables seem to open up a host of possibilities.

I've been reading up on this just now at www.sommarskog.se/arrays-in-sql-2005.html and Erland Sommarskog has a very similar approach, though creating a custom Numbers table, rather than using the one in master. This raised two questions in my mind.

1) how much difference does it make having all those redundant numbers to join against, if I will only ever use max 100 of them?

2) Erland's approach uses a CROSS JOIN, while yours is INNER JOIN. Instinct tells me that you should always use INNER JOIN where possible. Would you say that is true here?

Tack saa mycket fo"r hja"lpen!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 11:20:12

My INNER JOIN is essentially a CROSS JOIN because there is no relation between the two tables.
I just prefer writing INNER JOIN because then I also can write Type = 'P' on same line and doesn't have to out that in the WHERE clause.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 12:10:40

quote:
Originally posted by hamble18

Tack saa mycket fo"r hja"lpen!

You know Swedish! Wow, I'm impressed...

E 12°55'05.25"
N 56°04'39.16"

hamble18
Starting Member

12 Posts

Posted - 2008-08-20 : 17:50:11

hehe... du vet SQL ja"ttebra! Jag a"r ocksaa (errr....) impressed(!). Hmmm, my Swedish is *very* rusty!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources