Pivot Sql 2008 from Multiple Tables Need Help!

[pivots]

I currently have a database which is commonly used within our organization. We have a major issues which no one can seem to figure out so here it is.

I have two tables one called Cardholders and the other one is CustomFields. I have done an inner join on the tables to combine the fields into the same view. I then created a temp view table called "Pivot Temp" which is attached to the picture below(seems like new users can't upload pictures!).

I have a person who has multiple entries because he has many custom fields. I would like to know how can we pivot all the fields so they can be represented into ONE row.
In this new view table I have unique_id, LastName, FirstName, Active, AcitvationDate, ExpirationDate, CardCode, CustomFieldLabel, CustomFieldValue, AccessLevel.
In this case Pivot the CustomFieldLabel_TX to be a title and have the customFieldValue_TX as the value.
Currently there is one record per custom field value. For example John Doe looks like this.
Uni_id|LName|FirstName|Active|AcitvatDate|ExpDate|CardCode|CustomFieldLabel|CustomFieldValue|Access:
59|Doe|John|-1|2015-08-11|2020-08-11|222222|EmployeeID|90000|AllDoors
59|Doe|John|-1|2015-08-11|2020-08-11|222222|Floor|02|AllDoors
59|Doe|John|-1|2015-08-11|2020-08-11|222222|Department|Computers|AllDoors
59|Doe|John|-1|2015-08-11|2020-08-11|222222|Job Title|Tech|AllDoors

I would like to pivot these tables so it says:
Uni_id|LName|FirstName|Active|AcitvatDate|ExpDate|CardCode|EmployeeID|Floor|Department|JobTitle|Access:
59|Doe|John|-1|2015-08-11|2020-08-11|222222|90000|02|Computers|Tech|AllDoors

Any help is appreciated and if you have questions about the tables or fields please ask.

Mel

[Kristen]

@pivots

seems like new users can't upload pictures!

We much prefer text, formatted with the [</>] button, which we can cut & paste into worked examples etc.

; WITH MyCTE
AS
(
	SELECT	Uni_id, LName, FirstName, Active, AcitvatDate, ExpDate
		, CardCode, Access
		, CustomFieldLabel, CustomFieldValue
		, [RowNumber] = ROW_NUMBER() OVER
				(
					PARTITION BY CustomFieldLabel
					ORDER BY Uni_id
				)
	FROM	[Pivot Temp]
)
SELECT	Uni_id, LName, FirstName, Active, AcitvatDate, ExpDate
	, CardCode, Access
	, EmployeeID, Floor, Department, [Job Title]
FROM
(
	SELECT	RowNumber
		, Uni_id, LName, FirstName, Active, AcitvatDate
		, ExpDate, CardCode, Access
		, CustomFieldLabel, CustomFieldValue
FROM	MyCTE
) AS X
PIVOT
(
	max(CustomFieldValue)
	FOR	CustomFieldLabel IN (EmployeeID, Floor, Department, [Job Title])
) AS PivotTable

[hhrr]

Hi

Here's what i came up with .. Hope it helps

SELECT *
FROM (
	SELECT *
	FROM #temp
	) AS s
PIVOT(MAX(CustomFieldValue) FOR [CustomFieldLabel] IN (
			[EmployeeID]
			,[Floor]
			,[Department]
			,[Job Title]
			)) AS pvt

-- Below is the script to create the table
/*
DROP TABLE #Temp

CREATE TABLE #Temp (
Uni_id INT NULL
,LName VARCHAR(100) NULL
,FirstName VARCHAR(100) NULL
,Active INT NULL
,AcitvatDate DATETIME NULL
,ExpDate DATETIME NULL
,CardCode INT NULL
,CustomFieldLabel VARCHAR(100) NULL
,CustomFieldValue VARCHAR(100) NULL
,Access VARCHAR(100) NULL
)

INSERT INTO #temp
SELECT 59
,'Doe'
,'John'
,'-1'
,'2015-08-11'
,'2020-08-11'
,'222222'
,'EmployeeID'
,'90000'
,'AllDoors'

INSERT INTO #temp
SELECT 59
,'Doe'
,'John'
,'-1'
,'2015-08-11'
,'2020-08-11'
,'222222'
,'Floor'
,'02'
,'AllDoors'

INSERT INTO #temp
SELECT 59
,'Doe'
,'John'
,'-1'
,'2015-08-11'
,'2020-08-11'
,'222222'
,'Department'
,'Computers'
,'AllDoors'

INSERT INTO #temp
SELECT 59
,'Doe'
,'John'
,'-1'
,'2015-08-11'
,'2020-08-11'
,'222222'
,'Job Title'
,'Tech'
,'AllDoors'

-- select * from #temp
-- 59|Doe|John|-1|2015-08-11|2020-08-11|222222|90000|02|Computers|Tech|AllDoors
*/

[pivots]

Hi Kristen,

I appreciate the reply as you can see I'm a little new to the forum and SQL so I apologize for the incorrect formatting.

I tried what you have stated above and it worked to the extent of organizing the custom fields from columns to rows, however all the fields say Null now. I am on a test database so not to worry. Here is what I wrote for the code to correctly match all the fields. Any thoughts as to why they say null?

; WITH MyCTE
AS
(
SELECT UniqueID_IN, LastName_TX, FirstName_TX, Active_SI, ActivationDate_DT, ExpirationDate_DT
, CardCode_TX, AccessLevelLabel_TX
, CustomFieldLabel_TX, CustomFieldValue_TX
, [RowNumber] = ROW_NUMBER() OVER
(
PARTITION BY CustomFieldLabel_TX
ORDER BY UniqueID_IN
)
FROM [PivotTemp]
)
SELECT UniqueID_IN, LastName_TX, FirstName_TX, Active_SI, ActivationDate_DT, ExpirationDate_DT
, CardCode_TX, AccessLevelLabel_TX
, EmployeeID, Floor, Department, [Job Title]
FROM
(
SELECT RowNumber
UniqueID_IN, LastName_TX, FirstName_TX, Active_SI, ActivationDate_DT, ExpirationDate_DT
, CardCode_TX, AccessLevelLabel_TX
, CustomFieldLabel_TX, CustomFieldValue_TX
FROM MyCTE
) AS X
PIVOT
(
max(CustomFieldValue_TX)
FOR CustomFieldLabel_TX IN (EmployeeID, Floor, Department, [Job Title])
) AS PivotTable

[pivots]

Correction all the custom fields say Null the rest of the data on the other fields are still there.

[Kristen]

@pivots

I apologize for the incorrect formatting.

You can do this:

    ```
    your code here
    ```

In this snippet of code:

FROM
(
SELECT RowNumber
UniqueID_IN, 

you missed a comma between RowNumber and UniqueID_IN. Although I don't think that will make any difference to the outcome.

@pivots

it worked to the extent of organizing the custom fields from columns to rows, however all the fields say Null now

So it originally worked and gave you the correct PIVOT data?

but now:

1. The pivot report is showing NULLs?
   or
2. The data in you (test) database is all NULL?
   or
3. The layout of teh Pivot Report was OK but all the data was NULL from the first time you ran it.

for (2) then there must have been some sort of Update etc. as this PIVOT is a read-only operation.

Here's your code, your column names, but with [PivotTemp] table changed to temporary table #PivotTemp and some test data:

SELECT	*
INTO	#PivotTemp
FROM
(
SELECT	59 AS UniqueID_IN,
	'Doe' AS LastName_TX,
	'John' AS FirstName_TX,
	-1 AS Active_SI,
	'20150811' AS ActivationDate_DT,
	'20200811' AS ExpirationDate_DT,
	222222 AS CardCode_TX,
	'EmployeeID' AS CustomFieldLabel_TX,
	'90000' AS CustomFieldValue_TX,
	'AllDoors' AS AccessLevelLabel_TX
UNION ALL SELECT 59,'Doe','John',-1,'20150811','20200811',222222,'Floor','02','AllDoors'
UNION ALL SELECT 59,'Doe','John',-1,'20150811','20200811',222222,'Department','Computers','AllDoors'
UNION ALL SELECT 59,'Doe','John',-1,'20150811','20200811',222222,'Job Title','Tech','AllDoors'

UNION ALL SELECT 60,'Blogs','Joe',-1,'20150812','20200812',333333,'EmployeeID','90001','AllDoors'
UNION ALL SELECT 60,'Blogs','Joe',-1,'20150812','20200812',333333,'Floor','01','AllDoors'
UNION ALL SELECT 60,'Blogs','Joe',-1,'20150812','20200812',333333,'Department','Sales','AllDoors'
UNION ALL SELECT 60,'Blogs','Joe',-1,'20150812','20200812',333333,'Job Title','Rep','AllDoors'
) AS X

; WITH MyCTE
AS
(
	SELECT UniqueID_IN, LastName_TX, FirstName_TX, Active_SI, ActivationDate_DT, ExpirationDate_DT
		, CardCode_TX, AccessLevelLabel_TX
		, CustomFieldLabel_TX, CustomFieldValue_TX
		, [RowNumber] = ROW_NUMBER() OVER
	(
	PARTITION BY CustomFieldLabel_TX
	ORDER BY UniqueID_IN
)
FROM #PivotTemp
)
SELECT UniqueID_IN, LastName_TX, FirstName_TX, Active_SI, ActivationDate_DT, ExpirationDate_DT
	, CardCode_TX, AccessLevelLabel_TX
	, EmployeeID, Floor, Department, [Job Title]
FROM
(
	SELECT RowNumber
		, UniqueID_IN, LastName_TX, FirstName_TX, Active_SI, ActivationDate_DT, ExpirationDate_DT
		, CardCode_TX, AccessLevelLabel_TX
		, CustomFieldLabel_TX, CustomFieldValue_TX
FROM MyCTE
) AS X
PIVOT
(
	max(CustomFieldValue_TX)
	FOR CustomFieldLabel_TX IN (EmployeeID, Floor, Department, [Job Title])
) AS PivotTable
;
DROP TABLE #PivotTemp;

[Kristen]

Alternative reason for (2) is that the VIEW has changed and is selecting the data in a subtly different way now?

[pivots]

Kristen,

The first time I ran the query all of my fields except for the Custom Fields had data in it.
All the custom fields were NULLS. Then I went back into my application deleted a couple of fields and re-added new fields with some new data.
After re-running the query it seems like all the fields line up correctly, except for for the first custom field which is still NULL(Employee ID). Any ideas as to why?

[Kristen]

@pivots

except for for the first custom field which is still NULL(Employee ID). Any ideas as to why?

We'd need to see the data. Can you try adding some UNION ALL data to my sample above and see if you can get it to break?

Assuming I've got the data types right this should generate the UNION ALL statements for a list of IDs that you provide

SELECT	'UNION ALL SELECT '
	+ COALESCE(CONVERT(varchar(20), UniqueID_IN), 'NULL')
	+ ',' + COALESCE(''''+LastName_TX+'''', 'NULL')
	+ ',' + COALESCE(''''+FirstName_TX+'''', 'NULL')
	+ ',' + COALESCE(CONVERT(varchar(20), Active_SI), 'NULL')
	+ ',' + COALESCE(''''+CONVERT(varchar(8), ActivationDate_DT, 112)+'''', 'NULL')
	+ ',' + COALESCE(''''+CONVERT(varchar(8), ExpirationDate_DT, 112)+'''', 'NULL')
	+ ',' + COALESCE(''''+CONVERT(varchar(20), CardCode_TX)+'''', 'NULL')
	+ ',' + COALESCE(''''+CustomFieldLabel_TX+'''', 'NULL')
	+ ',' + COALESCE(''''+CustomFieldValue_TX+'''', 'NULL')
	+ ',' + COALESCE(''''+AccessLevelLabel_TX+'''', 'NULL')
FROM	[PivotTemp]
WHERE	UniqueID_IN IN (1234, 5678)