Create columns in a view from a select

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)

Create columns in a view from a select

Author

Topic

loydall
Starting Member

33 Posts

Posted - 2009-04-15 : 06:51:10

Hi - lets say I have a table with the following data:

id | fieldName
--------------
1 | Name
2 | Age
3 | Location
4 | Gender

And then I have a second table:

FKfieldId | FieldValue | UserId
--------------------------------
1 | John | 1
2 | 34 | 1
3 | London | 1
4 | M | 1
1 | Sally | 2
2 | 31 | 2
3 | New York | 2
4 | F | 2

So - you can see that the values from table 2 are based on the fields defined in table 1.

What I want is to be able to select from table 1 to generate a table with the correct number of columns based on the number of records and then populate those columns with data from table 2 - so the resulting select would generate:

Name | Age | Location |Gender
-------------------------------------------
John 34 London M
Sally 31 New York F

Any idea how I do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 06:59:11

Yes. Use dynamic SQL.

E 12°55'05.63"
N 56°04'39.26"

loydall
Starting Member

33 Posts

Posted - 2009-04-15 : 07:12:28

quote:
Originally posted by Peso

Yes. Use dynamic SQL.

E 12°55'05.63"
N 56°04'39.26"

Thanks - can you expand on that a little?

Cheers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 07:27:39

[code]CREATE TABLE #Table1
(
id INT,
fieldName VARCHAR(20)
)

INSERT #Table1
SELECT 1, 'Name' UNION ALL
SELECT 2, 'Age' UNION ALL
SELECT 3, 'Location' UNION ALL
SELECT 4, 'Gender'

CREATE TABLE #Table2
(
FKfieldId INT,
FieldValue VARCHAR(20),
UserId INT
)

INSERT #Table2
SELECT 1, 'John', 1 UNION ALL
SELECT 2, '34', 1 UNION ALL
SELECT 3, 'London', 1 UNION ALL
SELECT 4, 'M', 1 UNION ALL
SELECT 1, 'Sally', 2 UNION ALL
SELECT 2, '31', 2 UNION ALL
SELECT 3, 'New York', 2 UNION ALL
SELECT 4, 'F', 2

DECLARE @List VARCHAR(MAX),
@pivot VARCHAR(MAX)

SELECT @List = COALESCE(@List, 'SELECT p.UserID')
+ ',p.[' + CAST(id AS VARCHAR(11))
+ '] AS ' + QUOTENAME(fieldName)
FROM #Table1
ORDER BY id

SELECT @pivot = 'FROM #Table2 PIVOT (MAX(FieldValue) FOR FKFieldId IN ('

SELECT @pivot = @pivot + QUOTENAME(id) + ','
FROM #Table1
ORDER BY ID

SELECT @pivot = LEFT(@pivot, LEN(@pivot) - 1) + ')) AS p'

EXEC (@List + @Pivot)

DROP TABLE #Table1,
#Table2[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 07:31:28

Adn then this code to run the stored procedure above from a view

EXEC master..sp_addlinkedserver @server = 'loopback', @srvproduct = '', @provider='SQLNCLI', @datasrc = 'phbgpela\sql2008'
GO
CREATE VIEW vwTest
AS

SELECT	*
FROM	OPENQUERY(loopback, 'EXEC my_sp_above')
GO

SELECT	*
FROM	vwTest
GO

DROP VIEW vwTest
GO
EXEC master..sp_dropserver @server = 'loopback'
GO

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-15 : 09:07:09

Also see this in addition to Peso's post
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

loydall
Starting Member

33 Posts

Posted - 2009-04-15 : 09:25:30

Thanks,

I've tried that code and it works exactly as I want it to - odd thing is, when I apply it to the actual tables I'm using I get:

UserId Web Size CoName CoDescr
-------------------------------------------------
2103 NULL NULL Bobs Cars NULL
2103 NULL NULL NULL We sell Cars
2104 NULL NULL John's Computers NULL
2104 NULL NULL NULL We sell computers
2105 www.dfg.com NULL NULL NULL
2105 NULL 1 - 500 NULL NULL

So - I can't seem to get it to generate just 1 row per userId - you can see that we have a value for user 2103 for the CoName and that creates a row and then another row is created for CoDescr..

Like I say - your code works fine- it's just when I move it over to my existing tables...

Hmm.. Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 09:42:20

Post your ACTUAL code for the stored procedure. Maybe something happens during your transformation to real world tables?

E 12°55'05.63"
N 56°04'39.26"

loydall
Starting Member

33 Posts

Posted - 2009-04-15 : 09:51:49

CREATE TABLE [dbo].[TF_CustomFields_CustomFields](
[FieldId] [int] IDENTITY(1,1) NOT NULL,
[TableId] [int] NOT NULL,
[FieldName] [varchar](50) NOT NULL

)

CREATE TABLE [dbo].[TF_CustomFields_CustomFieldValues](
[ValueId] [int] IDENTITY(1,1) NOT NULL,
[FieldId] [int] NOT NULL,
[FieldContent] [varchar](max) NULL,
[PrimaryKeyValue] [varchar](50) NOT NULL
)

So - The 2nd table contains the values for the columns defined in the 1st table. FieldContent is the actual entry for that record - PrimaryKeyValue is used to extend other tables - mostly a user table so it's generally going to be a UserID value in that field (which is why I called it UserId)

And then the code I used:

DECLARE @List VARCHAR(MAX),
@pivot VARCHAR(MAX)

SELECT @List = COALESCE(@List, 'SELECT p.PrimaryKeyValue')
+ ',p.[' + CAST(FieldId AS VARCHAR(11))
+ '] AS ' + QUOTENAME(fieldName)
FROM TF_CustomFields_CustomFields
ORDER BY FieldId

SELECT @pivot = 'FROM TF_CustomFields_CustomFieldValues PIVOT (MAX(FieldContent) FOR TF_CustomFields_CustomFieldValues.FieldId IN ('

SELECT @pivot = @pivot + QUOTENAME(FieldId) + ','
FROM TF_CustomFields_CustomFields
ORDER BY FieldId

SELECT @pivot = LEFT(@pivot, LEN(@pivot) - 1) + ')) AS p'

EXEC (@List + @Pivot)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 09:54:59

Before EXEC, put two PRINT's there

PRINT @List
PRINT @pivot

Sometimes, in SQL Server 2005, there is a bug with ORDER BY and concatenated lists.

Then you copy and paste those two query parts into a query window and try to execute them.

E 12°55'05.63"
N 56°04'39.26"

loydall
Starting Member

33 Posts

Posted - 2009-04-15 : 09:57:45

SELECT p.PrimaryKeyValue,p.[1] AS [WebAddress],p.[2] AS [SizeOrganisation],p.[3] AS [CompanyName],p.[4] AS [CompanyDescription]
FROM TF_CustomFields_CustomFieldValues PIVOT (MAX(FieldContent) FOR TF_CustomFields_CustomFieldValues.FieldId IN ([1],[2],[3],[4])) AS p

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 09:58:35

What happens when you try to execute the code below?

quote:
Originally posted by loydall

SELECT p.PrimaryKeyValue,p.[1] AS [WebAddress],p.[2] AS [SizeOrganisation],p.[3] AS [CompanyName],p.[4] AS [CompanyDescription]
FROM TF_CustomFields_CustomFieldValues PIVOT (MAX(FieldContent) FOR TF_CustomFields_CustomFieldValues.FieldId IN ([1],[2],[3],[4])) AS p

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 10:00:40

Change this part
FOR TF_CustomFields_CustomFieldValues.FieldId IN ('
to
FOR FieldId IN ('

E 12°55'05.63"
N 56°04'39.26"

loydall
Starting Member

33 Posts

Posted - 2009-04-15 : 10:05:28

Same thing - I get a row per entry - not a row per primaryKeyId

Worth pointing out - the TableId column in the 1st Table allows me to define multiple sets of custom fields - it's a foreign key to a TableData table.

Not sure if that would effect things - essentially it means that if I have 4 columns defined in the 1st table - 2 of those Currently have tableId of 1 and 2 have TableID of 2. If you look at the results that I got earlier you'll see that the bottom two rows contain results for the 1st 2 columns - these are results that relate to a different TableId to the top results..

Hope that makes sense - not sure if it is affecting things though..

Thanks for all your help on this BTW..

loydall
Starting Member

33 Posts

Posted - 2009-04-15 : 11:31:17

Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 12:14:42

Provide proper sample data to add to #Table2 table above, to demonstrate your scenario.

E 12°55'05.63"
N 56°04'39.26"

loydall
Starting Member

33 Posts

Posted - 2009-04-15 : 12:50:18

The following is the current data from TF_CustomFields_CustomFieldValues

1 3 Bobs Cars 2103
2 4 We sell Cars 2103
3 3 John's Computers 2104
4 4 We sell computers 2104
5 1 www.dfg.com 2105
6 2 1 - 500 2105
9 5 1 smithg street 2103

And table 1 (TF_CustomFields_CustomFields) contains the following data:

1 1 WebAddress
2 1 SizeOrganisation
3 2 CompanyName
4 2 CompanyDescription
5 2 CompanyAddress

notmyrealname

98 Posts

Posted - 2009-04-15 : 13:22:19

Hi.

This works for me. The query shows Table1 but it is actually querying what you call Table2. You can take out the 2nd line (INTO TableResults) if you don't want the data inserted into a new table.

SELECT Table1.Name, T2.Age, T3.Location, T4.Gender
INTO TableResult
FROM Table1 INNER JOIN
(SELECT CAST(Name AS int) AS Age, UserID
FROM Table1 AS Table1_1
WHERE (ID = 2)) AS T2 ON Table1.UserID = T2.UserID INNER JOIN
(SELECT CAST(Name AS nvarchar(10)) AS Location, UserID
FROM Table1 AS Table1_2
WHERE (ID = 3)) AS T3 ON Table1.UserID = T3.UserID INNER JOIN
(SELECT CAST(Name AS nvarchar(10)) AS Gender, UserID
FROM Table1 AS Table1_3
WHERE (ID = 4)) AS T4 ON Table1.UserID = T4.UserID
WHERE (Table1.ID = 1)

I'm assuming the list you have will always be complete. I.e. there will always be groups of four consecutive rows for each person. This would work even if they were jumbled up though.

Hope this helps.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 13:33:10

Works for me

CREATE TABLE	#Table1
		(
			fieldID INT,
			tableID INT,
			fieldName VARCHAR(20)
		)

INSERT	#Table1
SELECT	1, 1, 'WebAddress' UNION ALL
SELECT	2, 1, 'SizeOrganisation' UNION ALL
SELECT	3, 2, 'CompanyName' UNION ALL
SELECT	4, 2, 'CompanyDescription' UNION ALL
SELECT	5, 2, 'CompanyAddress'

CREATE TABLE	#Table2
		(
			valueID INT,
			fieldID INT,
			fieldContent VARCHAR(MAX),
			primaryKeyValue VARCHAR(50)

		)

INSERT	#Table2
SELECT	1, 3, 'Bobs Cars', 2103 UNION ALL
SELECT	2, 4, 'We sell Cars', 2103 UNION ALL
SELECT	3, 3, 'John''s Computers', 2104 UNION ALL
SELECT	4, 4, 'We sell computers', 2104 UNION ALL
SELECT	5, 1, 'www.dfg.com', 2105 UNION ALL
SELECT	6, 2, '1 - 500', 2105 UNION ALL
SELECT	9, 5, '1 smithg street', 2103

DECLARE	@List VARCHAR(MAX),
	@pivot VARCHAR(MAX)

SELECT		@List = COALESCE(@List, 'SELECT p.primaryKeyValue')
			+ ',p.' + QUOTENAME(fieldID)
			+ ' AS ' + QUOTENAME(fieldName)
FROM		#Table1
ORDER BY	fieldID

SELECT	@pivot = 'FROM (select fieldid, fieldcontent, primarykeyvalue from #Table2) as t2',
	@pivot = @pivot + ' PIVOT (MAX(fieldContent) FOR FieldID IN ('

SELECT		@pivot = @pivot + QUOTENAME(fieldID) + ','
FROM		#Table1
ORDER BY	fieldID

SELECT	@pivot = LEFT(@pivot, LEN(@pivot) - 1) + ')) AS p'

EXEC (@List + @Pivot)

DROP TABLE	#Table1,
		#Table2

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources