Cross Tab in SQL 2K ?? 2

[ajacode]

How to Update from Single Row/Many columns to Single Column/Many Rows

In SQL Server2000

I want to try to create a stored procedure that uses the following select statement which pulls all the column names from a single row in a table: We'll Call it A

SELECT name
FROM syscolumns
WHERE [id] = OBJECT_ID('dbo.A')

To create a Recordset and then Join those records to rows in an existing table (Lets Call it B) that has a field called eField which holds all the names of these columns

I then want to take the values from Table A and update them into a corresponding field eValue in B .

Basically this is a transformation but I don't know the syntax I want to take a row of values and turn them into a column of values.

Here's Table A

Folder ID Apples Peaches Plums
_______________________________________
000011 Red Yellow Purple


I want to insert it into this table B

FruitID Color
__________________________
Apple
Peaches
Plums


and End Up with

FruitID Color
__________________________
Apple Red
Peaches Yellow
Plums Purple

By Grabbing the Colum Names from A and matching them to the Values in B.FruitID

And Update B.Color to the Values in Table A Where FolderID = 000011

I hope I am making sense.

Thanks Beforehand

AJ

 

[zhavic]

Try this ( based on your example )
( but it may be slow - based on count of columns in Table A )

DECLARE @cSQL nvarchar(4000)
DECLARE @cTablename_A varchar(50)
DECLARE @cTablename_B varchar(50)
DECLARE @cTable_A_ID_column varchar(50)
DECLARE @cTable_B_ID_column varchar(50)

SET @cSQL = ''
SET @cTablename_A = '[JDM]'	-- put the name of your table A
SET @cTablename_B = '[B]'	-- put the name of your table B
SET @cTable_A_ID_column = 'FolderID'
SET @cTable_B_ID_column = 'FruitID'

/* create SQL statement to create CROSSTAB table ( using UNIONs ) */
SELECT @cSQL = @cSQL + 'SELECT ''' + [name] + ''' AS [ID], '
								   + [name] + ' AS Color '
							+ 'FROM dbo.' + @cTablename_A + ' AS [A] '
							+ 'WHERE [A].' + @cTable_A_ID_column + ' = @ID ' 
							+ 'UNION '
FROM syscolumns
WHERE [id] = OBJECT_ID( 'dbo.' + @cTablename_A )

/* cut out last UNION string */
SET @cSQL = LEFT( @cSQL, LEN( @cSQL ) - LEN( 'UNION ' ) )

/* add UPDATE statement ( using as subquery CROSSTAB table from the above ) */
SET @cSQL = 'UPDATE ' + @cTablename_B + ' SET [Color] = [A].[Color] '
				+ 'FROM ' + @cTablename_B + ' AS [B] '
			    + 'INNER JOIN ( ' + @cSQL + ' ) AS [A] ON [A].[ID] = [B].' + @cTable_B_ID_column

PRINT @cSQL

/* execute query */
EXECUTE sp_executesql @cSQL,
			N'@ID char(6)',		-- place there right type of your ID column in Table B
			'000011'			-- ID value of row for Table A  ( based on your example )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[ajacode]

This looks promising I'll let you know how it comes out

Thanks for the Quick reply, Zhavic

 

[ajacode]

Zhavic ,

IN this Segment
SELECT @cSQL = @cSQL + 'SELECT ''' + [name] + ''' AS [ID], '
+ [name] + ' AS Color '
+ 'FROM dbo.' + @cTablename_A + ' AS [A] '
+ 'WHERE [A].' + @cTable_A_ID_column + ' = @ID '
+ 'UNION '
FROM syscolumns
WHERE [id] = OBJECT_ID( 'dbo.' + @cTablename_A )

IT turns out that each column name from Table a comes out in SQL as both the ID and Color colum.

What I am really trying to get is and ID column value say based on an Inbound Parameter varFolderID

SO the ID column would be set to the parameter varaibale and the color would be the column name.

I think my original question was a bit misleading.

HOw would you make the change I noted above?

Thanks

AJA

 

[ajacode]

HI Zhavic,

Here's what I came up with using your structure and my real table names.....
+++++++++++++++++++++++++++++++++++++++++++++++++++++++

SET NOCOUNT ON


DECLARE @tab TABLE (Field VARCHAR(128), Value VARCHAR(250) )
DECLARE @sql NVARCHAR(900)
DECLARE @FolderID VARCHAR(50)

SET @FolderID = '0000000000000000000000000000012'

--This is the Actual Value in my table for eFolderID
--But look below why is it truncated? The source field
-- is eFolderID is Varchar (31) and I even put it to
--50 here but it still truncs the value??


DECLARE @name VARCHAR(128)
DECLARE @Value VARCHAR(250)
DECLARE cur CURSOR FORWARD_ONLY FOR
SELECT name
FROM syscolumns
WHERE [id] = OBJECT_ID('dbo.CHF_TDM_Primary')
-- AND name != 'eFolderID'
OPEN cur

FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS = 0 BEGIN
SET @sql = 'SELECT @Value = ' + @name + ' FROM CHF_TDM_Primary WHERE eFOLDERID = ''' +
@FolderID + ''''
EXEC sp_executesql @sql, N'@Value VARCHAR(20) OUTPUT', @Value OUTPUT
INSERT INTO @tab (Field, Value) VALUES (@name, @value)
FETCH NEXT FROM cur INTO @name
END

CLOSE cur
DEALLOCATE cur


SELECT * FROM @tab
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Here's what comes back for eFOLDERID

EFOLDERID 00000000000000000000

--This cam out truncated Any Idea?
--The rest came out fine

cboDTEFNoReason No Benefits for EF t
cboDTHDLRate < 45 Men
cboDTLDLRate < 45 Men
cboDTTotaCholRate < 200 mg/dl

 

[zhavic]

Hi Aja, sorry for my late.

The value of EFOLDERID column comes truncated because of 'VARCHAR(20)' in this part:
EXEC sp_executesql @sql, N'@Value VARCHAR(20) OUTPUT'
You need to set it to min. 31

Also I thing you was not a misleading.
IN this Segment I do the same you do in your code with cursor:

SELECT @cSQL = @cSQL + 'SELECT ''' + [name] + ''' AS [ID], '
+ [name] + ' AS Color '
+ 'FROM dbo.' + @cTablename_A + ' AS [A] '
+ 'WHERE [A].' + @cTable_A_ID_column + ' = @ID '
+ 'UNION '
FROM syscolumns
WHERE [id] = OBJECT_ID( 'dbo.' + @cTablename_A )
AND name != 'eFolderID' -- you don't need eFolderID column from table A ( from your previous post )

This part 'SELECT ''' + [name] + ''' AS [ID], ' has quotas around the [name], so this makes [ID] to has column name from Table A
and this: [name] + ' AS Color ' makes the Color column has the value of column with the [name] from Table A,
so in simple, if the Table A is CHF_TDM_Primary, and @cTable_A_ID_column is eFOLDERID, than it makes SQL like this:

Based on This:
Folder ID Apples Peaches Plums
_______________________________________
000011 Red Yellow Purple

SQL will be:

SELECT 'Apples' AS [ID], Apples AS Color FROM CHF_TDM_Primary WHERE eFOLDERID = @ID
UNION
SELECT 'Peaches' AS [ID], Peaches AS Color FROM CHF_TDM_Primary WHERE eFOLDERID = @ID
UNION
SELECT 'Plums' AS [ID], Plums AS Color FROM CHF_TDM_Primary WHERE eFOLDERID = @ID

EXECUTE sp_executesql @cSQL,
N'@ID varchar(50)',
'0000000000000000000000000000012'

So when you run this SQL, you get table like this:

[ID] Color
--------------------
Apples Red
Peaches Yellow
Plums Purple


The next part of my code creates UPDATE statement like this:

UPDATE Table_B SET [Color] = [A].[Color]
FROM Table_B AS
INNER JOIN (
SELECT 'Apples' AS [ID], Apples AS Color FROM CHF_TDM_Primary WHERE eFOLDERID = @ID
UNION
SELECT 'Peaches' AS [ID], Peaches AS Color FROM CHF_TDM_Primary WHERE eFOLDERID = @ID
UNION
SELECT 'Plums' AS [ID], Plums AS Color FROM CHF_TDM_Primary WHERE eFOLDERID = @ID
) AS [A] ON [A].[ID] = .Table_B_ID_column


If you have any question, post it there.

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.