0

I am trying to work on a database with 3 tables. To make it easier I have created a couple of temp tables to work out the syntax.

CREATE TABLE #owner
(
[NameId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)

INSERT INTO #owner VALUES ('Thanatoid');
INSERT INTO #owner VALUES ('Other');

CREATE TABLE #propertyType
(
[TypeId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)

INSERT INTO #propertyType VALUES ('Home');
INSERT INTO #propertyType VALUES ('Car');

CREATE TABLE #property
(
[NameId] tinyint NOT NULL,
[TypeId] tinyint NOT NULL,
[Value] varchar(50) NOT NULL
)

INSERT INTO #property VALUES (1,1, 'Blue');
INSERT INTO #property VALUES (1,2, 'Black');
INSERT INTO #property VALUES (2,1, 'Red');
INSERT INTO #property VALUES (2,2, 'Black');

DROP TABLE #owner;
DROP TABLE #propertyType;
DROP TABLE #property

| NameId | Name |
| 1 | Thanatoid|
| 2 | other |

| TypeId | Name |
| 1 | Home |
| 2 | Car |

| NameId | TypeId | Value |
| 1 | 1 | Blue |
| 1 | 2 | Black |
| 2 | 1 | Red |
| 2 | 2 | Black |

Where property value is some arbitrary detail. The real propertyType has 50 or 60 rows and not every property has all of the values. I am trying to create a pivot table that would look like so that I can query the data and present the data easier:

[Owner | Home | Car ]
[Thanatoid | Blue | Black ]
[Other| Red | Black ]

The propertyTypes are added often, and I don't really have the ability to change them. There is a unique constrant on property on nameid and typeid so there will never be two of the same property with the same owner. Any help would be very helpful.

2
Contributors
2
Replies
3
Views
9 Years
Discussion Span
Last Post by Thanatoid
0

This thread has been marked solved so it means you know now how to do it. I am stuck with something almost like this. If you have solved this can you please tell me how to.
Thanks.

0

This thread has been marked solved so it means you know now how to do it. I am stuck with something almost like this. If you have solved this can you please tell me how to.
Thanks.

What are you trying to do. What I did was this:
SET @pt = STUFF(
(
SELECT CAST(',' AS VARCHAR(MAX)) + CAST(QUOTENAME([ProfileItemType].[Name]) AS VARCHAR(MAX))
FROM [Table]

FOR XML PATH('')
), 1, 1, '')

SET @sql = '
SELECT *
FROM
(
INNER SELECT HERE
) AS t
PIVOT
(
MIN([Value])
FOR pt_name IN (' + @pt + N')
) AS pvt'

EXEC sp_executesql @sql;

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.