IF OBJECT_ID('TempDB..#Field','U') IS NOT NULL
         DROP TABLE #Field
 
CREATE TABLE #Field
(
	fieldID int NOT NULL,
	pkFromOrigTable int NOT NULL,
	fieldName varchar(200) not null,
	fieldValue decimal(30,20)
)

/* Pivot Query to display Profile Properties */
/* Note: Scripts to create and populate test tables below */

DECLARE @UserID AS INT
SET @UserID = 1
	
    SELECT
        *
    FROM
        (
         SELECT
            pd.Name AS PropertyName
           ,up.PropertyValue AS PropertyValue
         FROM
            dbo.TEST_Users AS u
         INNER JOIN dbo.TEST_UserProfile AS up
            ON up.UserID = u.userid
         INNER JOIN dbo.TEST_ProfileDefinition AS pd
            ON up.DefinitionID = pd.DefinitionID
         WHERE
            u.UserID = @UserID
        ) AS Result PIVOT
		(MAX(PropertyValue) FOR PropertyName IN 
		([FirstName],[LastName],[StreetAddress],[City],[State],[Zip],[FavoriteColor],[PIN])) 
	AS UserProfile



/* Run Once */

CREATE TABLE [dbo].[TEST_Users](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[Username] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Test_Users] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[TEST_Users] ON
INSERT [dbo].[TEST_Users] ([UserID], [Username]) VALUES (1, N'George Washington')
INSERT [dbo].[TEST_Users] ([UserID], [Username]) VALUES (2, N'John Adams')
INSERT [dbo].[TEST_Users] ([UserID], [Username]) VALUES (3, N'Thomas Jefferson')
INSERT [dbo].[TEST_Users] ([UserID], [Username]) VALUES (4, N'James Madison')
SET IDENTITY_INSERT [dbo].[TEST_Users] OFF


CREATE TABLE [dbo].[TEST_UserProfile](
	[ProfileID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[DefinitionID] [int] NOT NULL,
	[PropertyValue] [nvarchar](max) NULL,
 CONSTRAINT [PK_TEST_UserProfile] PRIMARY KEY CLUSTERED 
(
	[ProfileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[TEST_UserProfile] ON
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (1, 1, 1, N'George')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (2, 1, 2, N'Washington')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (3, 1, 3, N'123 Main St')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (4, 1, 4, N'Mt Vernon')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (5, 1, 5, N'VA')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (6, 1, 6, N'34567')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (7, 1, 7, N'Blue')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (8, 1, 8, N'1776')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (10, 2, 1, N'John')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (11, 2, 2, N'Adams')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (12, 2, 3, N'456 Elm Ln')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (13, 2, 4, N'Boston')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (14, 2, 5, N'MA')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (15, 2, 6, N'01234')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (16, 2, 7, N'Red')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (17, 2, 8, N'1799')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (18, 3, 1, N'Thomas')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (19, 3, 2, N'Jefferson')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (20, 3, 3, N'RR 567')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (21, 3, 4, N'Charlottesville')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (22, 3, 5, N'VA')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (23, 3, 6, N'23456')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (24, 3, 7, N'Green')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (25, 3, 8, N'1801')
SET IDENTITY_INSERT [dbo].[TEST_UserProfile] OFF


CREATE TABLE [dbo].[TEST_ProfileDefinition](
	[DefinitionID] [int] IDENTITY(1,1) NOT NULL,
	[Category] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[DataType] [nvarchar](50) NOT NULL,
	[Length] [int] NOT NULL,
	[ViewOrder] [int] NOT NULL,
 CONSTRAINT [PK_TEST_ProfileDefinition] PRIMARY KEY CLUSTERED 
(
	[DefinitionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[TEST_ProfileDefinition] ON
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (1, N'Personal', N'FirstName', N'varchar', 50, 10)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (2, N'Personal', N'LastName', N'varchar', 50, 20)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (3, N'Address', N'StreetAddress', N'varchar', 50, 10)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (4, N'Address', N'City', N'varchar', 20, 20)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (5, N'Address', N'State', N'char', 2, 30)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (6, N'Address', N'Zip', N'varchar', 20, 40)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (7, N'User', N'FavoriteColor', N'varchar', 50, 10)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (8, N'User', N'PIN', N'int', 4, 20)
SET IDENTITY_INSERT [dbo].[TEST_ProfileDefinition] OFF

create table employee
(
   employee_id smallint not null primary key,
   ssn char(12) not null,
   hire_date datetime not null,
   reportsto_id smallint null
);

create table employee_extype
(
   primary key (employee_id, extype_id ),
   employee_id smallint not null,
   extype_id smallint not null,
   exttype_xml XML null
);