Adjust how Data is Returned in Table

USE ProjectServer_Published

SELECT l.LT_NAME AS Table_Name,
MSP_LOOKUP_TABLE_VALUES_PUBLISHED_VIEW.LT_VALUE_TEXT AS 'Lookup Table Value'
FROM dbo.MSP_LOOKUP_TABLES_PUBLISHED_VIEW l INNER JOIN
dbo.MSP_LOOKUP_TABLE_VALUES_PUBLISHED_VIEW
ON l.LT_UID = MSP_LOOKUP_TABLE_VALUES_PUBLISHED_VIEW.LT_UID
WHERE l.LT_NAME = 'Project Status' OR
l.LT_NAME = 'Project Phase'
ORDER BY LT_NAME, 'Lookup Table Value'

Table_Name Lookup Table Value
Project Phase Build
Project Phase Closure
Project Phase Implementation
Project Phase Initiate
Project Phase Plan
Project Phase Transition
Project Status Cancelled
Project Status Complete
Project Status In Progress
Project Status Logged
Project Status On Hold

wwalkerbout (9/16/2012)

Hi,

Messing around withn SQL queries on a Sunday, so I have another question (posted my fist one this morning). This time, its about the way the data is set out in the returned Table. I am working with SQL Server 2008 R2 and using Microsoft SQL Management Studio. So I'm looking at the way the table is displayed when I execute my query.

The query in question is pulling Lookup Table names and values from the Project Server 2010 ProjectServer_Published database. The query is listed below:


USE ProjectServer_Published

SELECT   l.LT_NAME AS Table_Name,
         MSP_LOOKUP_TABLE_VALUES_PUBLISHED_VIEW.LT_VALUE_TEXT AS 'Lookup Table Value'
FROM     dbo.MSP_LOOKUP_TABLES_PUBLISHED_VIEW l INNER JOIN 
         dbo.MSP_LOOKUP_TABLE_VALUES_PUBLISHED_VIEW 
ON       l.LT_UID = MSP_LOOKUP_TABLE_VALUES_PUBLISHED_VIEW.LT_UID
WHERE	 l.LT_NAME = 'Project Status' OR
		 l.LT_NAME = 'Project Phase'
ORDER BY LT_NAME, 'Lookup Table Value'

The result of this query is:

Table_Name	Lookup Table Value
Project Phase	Build
Project Phase	Closure
Project Phase	Implementation
Project Phase	Initiate
Project Phase	Plan
Project Phase	Transition
Project Status	Cancelled
Project Status	Complete
Project Status	In Progress
Project Status	Logged
Project Status	On Hold

Is there a way to change the query so that I can have each 'Table_Name' displayed as the column header and the values displayed in the column below each Table_Name?

Any suggestions on how I would go about doing this would be appreciated.

Cheers,

Wayne

;WITH PriorQueryResults AS (
SELECT Table_Name='Project Phase', [Lookup Table Value]= 'Build'
UNION ALL SELECT 'Project Phase','Closure'
UNION ALL SELECT 'Project Phase','Implementation'
UNION ALL SELECT 'Project Phase','Initiate'
UNION ALL SELECT 'Project Phase','Plan'
UNION ALL SELECT 'Project Phase','Transition'
UNION ALL SELECT 'Project Status','Cancelled'
UNION ALL SELECT 'Project Status','Complete'
UNION ALL SELECT 'Project Status','In Progress'
UNION ALL SELECT 'Project Status','Logged'
UNION ALL SELECT 'Project Status','On Hold'
),
AddRowNum AS (
SELECT Table_Name, [Lookup Table Value]
,n=ROW_NUMBER() OVER (PARTITION BY Table_Name ORDER BY [Lookup Table Value])
FROM PriorQueryResults
)
SELECT [Project Phase]=MAX(CASE Table_Name WHEN 'Project Phase' THEN [Lookup Table Value] ELSE '' END)
,[Project Status]=MAX(CASE Table_Name WHEN 'Project Status' THEN [Lookup Table Value] ELSE '' END)
FROM AddRowNum
GROUP BY n