SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adjust how Data is Returned in Table


Adjust how Data is Returned in Table

Author
Message
wwalkerbout
wwalkerbout
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 45
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90169 Visits: 45284
Read up on the PIVOT keyword.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89337 Visits: 41143
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


Not sure what you want for an output on this, Wayne. Could you post your result above as you'd actualy like to see it?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
wwalkerbout
wwalkerbout
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 45
Hi Gail, Jeff,

Gail: Thanks for the tip. Pivoting the data certainly sounds like what I am after. I'll take a look at that behavior.

Jeff: The result set I would like is as follows:



Project Phase Project Status
------------- --------------
Build Cancelled
Closure Complete
Implementation In Progress
Initiate Logged
Plan On Hold
Transition



I appreciate your feedback.

Cheers,

Wayne
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7555 Visits: 6431
How about something like this?


;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





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
wwalkerbout
wwalkerbout
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 45
Hi Dwain,

Thanks very much for going to the trouble of setting this all out for me. I'm very new (well, very rusty after 15 years of non-use), to playing with SQL Queries again and any such help as this is a big step forward for me.

Very much appreciated. I'll test it out with my scenario when I get to the office tomorrow.

Cheers,

Wayne
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search