Access Transform statement to SQL equivalent

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 2000 Forums

SQL Server Development (2000)

Access Transform statement to SQL equivalent

Author

Topic

Krat0s
Starting Member

5 Posts

Posted - 2007-10-04 : 12:41:52

Hi folks, new here and somewhat of a SQL novice so please bear with me.
I've got an Access solution with which I am able to get a dynamic crosstab solution with the Transform statement but I need the equivalent for SQL Server 2000.
I've searched extensively through the forums and have been able to find some complicated code which requires aggregation but nothing I can use. For the advanced SQL persons and Gurus, this should be a piece of cake.

Here are the visuals adn Access SQL.

From This:

ID FIELD_ID VALUE
--------------------------------
290 3 John
290 9 10562
290 8 -1
291 8 -1
291 4 Doe
291 3 John
291 9 10562
292 3 John
292 9 10562
292 8 -1
292 4 Doe
293 4 Dear
293 9 11111
293 8 -1
293 3 Jane
294 8 -1
294 4 Dear
294 3 Jane
294 9 11111
295 8 -1
295 9 11111
295 4 Dear
295 3 Jane

I want to be able to get this using SQL Server SQL (I'm able to do so with with Access 2K3)

ID 3 4 5 6 7 8 9
----------------------------------------------------------------
290 John -1 10562
291 John Doe -1 10562
292 John Doe -1 10562
293 Jane Dear -1 11111
294 Jane Dear -1 11111
295 Jane Dear -1 11111

Here's the Access SQL:

TRANSFORM First(Test.VALUE) AS FirstOfVALUE
SELECT Test.ID
FROM Test
GROUP BY Test.ID
ORDER BY Test.FIELD_ID
PIVOT Test.FIELD_ID

Krat0s
Starting Member

5 Posts

Posted - 2007-10-08 : 09:19:25

Any suggestions.....Anyone?
Is this really that complicated?

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 09:38:22

I got to this, but it doesn't work, but I seem to remember something like this (intermediate viarables to "concatenate" fuller values) being possible.

maybe someone else can remember


DECLARE	@InData TABLE
(
	ID int NOT NULL,
	FIELD_ID int NOT NULL,
	VALUE varchar(10),
	PRIMARY KEY
	(
		ID,
		FIELD_ID
	)
)
INSERT INTO @InData
SELECT *
FROM
(
SELECT	[ID] = 290, [FIELD_ID] = 3, [VALUE] = 'John' UNION ALL
SELECT	290, 9, '10562' UNION ALL
SELECT	290, 8, '-1' UNION ALL
SELECT	291, 8, '-1' UNION ALL
SELECT	291, 4, 'Doe' UNION ALL
SELECT	291, 3, 'John' UNION ALL
SELECT	291, 9, '10562' UNION ALL
SELECT	292, 3, 'John' UNION ALL
SELECT	292, 9, '10562' UNION ALL
SELECT	292, 8, '-1' UNION ALL
SELECT	292, 4, 'Doe' UNION ALL
SELECT	293, 4, 'Dear' UNION ALL
SELECT	293, 9, '11111' UNION ALL
SELECT	293, 8, '-1' UNION ALL
SELECT	293, 3, 'Jane' UNION ALL
SELECT	294, 8, '-1' UNION ALL
SELECT	294, 4, 'Dear' UNION ALL
SELECT	294, 3, 'Jane' UNION ALL
SELECT	294, 9, '11111' UNION ALL
SELECT	295, 8, '-1' UNION ALL
SELECT	295, 9, '11111' UNION ALL
SELECT	295, 4, 'Dear' UNION ALL
SELECT	295, 3, 'Jane'
) AS X
ORDER BY [ID], [FIELD_ID], [VALUE]

DECLARE	@OutData TABLE
(
	ID int NOT NULL,
	VALUE varchar(20),
	PRIMARY KEY
	(
		ID
	)
)

DECLARE	@LastID		int,
	@StrValue	varchar(20)	-- Concatenated values

-- Create target rows, with no VALUE
INSERT INTO @OutData(ID)
SELECT DISTINCT ID
FROM	@InData
ORDER BY ID


SELECT	*
FROM	@OutData

-- Initial values (to force "changed PK" event)
SELECT	@LastID		= -1,
	@StrValue	= NULL

-- Update Value in @OutData
UPDATE	U
SET
	@StrValue = CASE WHEN @LastID = I.ID THEN @StrValue ELSE NULL END,
	@StrValue = COALESCE(@StrValue + ' ', '') + I.Value,
	U.Value = @StrValue,
	@LastID = I.ID
FROM	@InData AS I
	JOIN @OutData AS U
		 ON U.ID = I.ID

SELECT	*
FROM	@OutData

Kristen

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-08 : 10:08:30

not exactly clear on what you're asking (maybe it's just the layout of data in your question). will this do it (using kristens DDL)...

select i.ID,
f1.value as FieldID_1,
f2.value as FieldID_2,
f3.value as FieldID_3,
f4.value as FieldID_4,
f5.value as FieldID_5,
f6.value as FieldID_6,
f7.value as FieldID_7,
f8.value as FieldID_8,
f9.value as FieldID_9
from (select distinct ID from @indata) i
left join (select ID, field_id, value from @indata where field_id = 1) f1 on f1.ID = i.ID
left join (select ID, field_id, value from @indata where field_id = 2) f2 on f2.ID = i.ID
left join (select ID, field_id, value from @indata where field_id = 3) f3 on f3.ID = i.ID
left join (select ID, field_id, value from @indata where field_id = 4) f4 on f4.ID = i.ID
left join (select ID, field_id, value from @indata where field_id = 5) f5 on f5.ID = i.ID
left join (select ID, field_id, value from @indata where field_id = 6) f6 on f6.ID = i.ID
left join (select ID, field_id, value from @indata where field_id = 7) f7 on f7.ID = i.ID
left join (select ID, field_id, value from @indata where field_id = 8) f8 on f8.ID = i.ID
left join (select ID, field_id, value from @indata where field_id = 9) f9 on f9.ID = i.ID

Em

Krat0s
Starting Member

5 Posts

Posted - 2007-10-08 : 11:38:16

The layout of our data is the underlying issue here and that's why the result of the pivot/x-tab has to be dynamic. The number of fields is not limited to 9 and may vary over time, therefore, there should be no hard coding of Id's.
The recordset I need is handled beautifully by the Access TRANSFORM function and the equivalent of which I'm trying to mimic on SQL Server 2K.
To further clarify, here's more.
The first "ID" is a Form which has x number fields (represented by the field_id), a majority of which are optional. I need a dynamic data set which lists all forms with whatever fields have been entered regardless of how many fields exist.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 11:50:44

[code]-- Prepare sample data
CREATE TABLE #Sample
(
ID INT,
FieldID INT,
Value VARCHAR(10)
)

INSERT #Sample
SELECT 290, 3, 'John' UNION ALL
SELECT 290, 9, '10562' UNION ALL
SELECT 290, 8, '-1' UNION ALL
SELECT 291, 8, '-1' UNION ALL
SELECT 291, 4, 'Doe' UNION ALL
SELECT 291, 3, 'John' UNION ALL
SELECT 291, 9, '10562' UNION ALL
SELECT 292, 3, 'John' UNION ALL
SELECT 292, 9, '10562' UNION ALL
SELECT 292, 8, '-1' UNION ALL
SELECT 292, 4, 'Doe' UNION ALL
SELECT 293, 4, 'Dear' UNION ALL
SELECT 293, 9, '11111' UNION ALL
SELECT 293, 8, '-1' UNION ALL
SELECT 293, 3, 'Jane' UNION ALL
SELECT 294, 8, '-1' UNION ALL
SELECT 294, 4, 'Dear' UNION ALL
SELECT 294, 3, 'Jane' UNION ALL
SELECT 294, 9, '11111' UNION ALL
SELECT 295, 8, '-1' UNION ALL
SELECT 295, 9, '11111' UNION ALL
SELECT 295, 4, 'Dear' UNION ALL
SELECT 295, 3, 'Jane'

DECLARE @SQL VARCHAR(8000)

SET @SQL = 'SELECT ID'

SELECT @SQL = @SQL + ',MAX(CASE WHEN FieldID=' + d.FieldID + 'THEN Value END)AS[' + d.FieldID + ']'
FROM (
SELECT DISTINCT TOP 100 PERCENT
CAST(FieldID AS VARCHAR(12)) AS FieldID
FROM #Sample
ORDER BY FieldID
) AS d

SET @SQL = @SQL + 'FROM #Sample GROUP BY ID ORDER BY ID'
PRINT @SQL

EXEC (@SQL)

DROP TABLE #Sample[/code]

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

Krat0s
Starting Member

5 Posts

Posted - 2007-10-08 : 14:34:53

Thank You Peso!
Your code did the trick.

Cheers!

Krat0s
Starting Member

5 Posts

Posted - 2007-10-08 : 14:46:15

Just one last thing. What's the best way to use this? What if I needed to pass parameters?

quote:

Originally posted by Peso

-- Prepare sample data
CREATE TABLE	#Sample
		(
			ID INT,
			FieldID INT,
			Value VARCHAR(10)
		)

INSERT	#Sample
SELECT	290, 3, 'John' UNION ALL
SELECT	290, 9, '10562' UNION ALL
SELECT	290, 8, '-1' UNION ALL
SELECT	291, 8, '-1' UNION ALL
SELECT	291, 4, 'Doe' UNION ALL
SELECT	291, 3, 'John' UNION ALL
SELECT	291, 9, '10562' UNION ALL
SELECT	292, 3, 'John' UNION ALL
SELECT	292, 9, '10562' UNION ALL
SELECT	292, 8, '-1' UNION ALL
SELECT	292, 4, 'Doe' UNION ALL
SELECT	293, 4, 'Dear' UNION ALL
SELECT	293, 9, '11111' UNION ALL
SELECT	293, 8, '-1' UNION ALL
SELECT	293, 3, 'Jane' UNION ALL
SELECT	294, 8, '-1' UNION ALL
SELECT	294, 4, 'Dear' UNION ALL
SELECT	294, 3, 'Jane' UNION ALL
SELECT	294, 9, '11111' UNION ALL
SELECT	295, 8, '-1' UNION ALL
SELECT	295, 9, '11111' UNION ALL
SELECT	295, 4, 'Dear' UNION ALL
SELECT	295, 3, 'Jane'

DECLARE	@SQL VARCHAR(8000)

SET	@SQL = 'SELECT ID'

SELECT	@SQL = @SQL + ',MAX(CASE WHEN FieldID=' + d.FieldID + 'THEN Value END)AS[' + d.FieldID + ']'
FROM	(
		SELECT DISTINCT TOP 100 PERCENT
				CAST(FieldID AS VARCHAR(12)) AS FieldID
		FROM		#Sample
		ORDER BY	FieldID
	) AS d

SET	@SQL = @SQL + 'FROM #Sample GROUP BY ID ORDER BY ID'
PRINT	@SQL

EXEC	(@SQL)

DROP TABLE	#Sample

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

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 14:58:02

"What's the best way to use this? What if I needed to pass parameters?"

Sounds like you need a Stored Procedure

But given that it is using Dynamic SQL you could construct the SQL syntax client site, and then run using, say, sp_ExecuteSQL so that the query plan is cached - as much as is possible.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources