How to Display Data Vertically?

Question

How to Display Data Vertically?

Shamshad Ali

SSCarpal Tunnel

Points: 4822
More actions
December 22, 2008 at 6:53 am

#71118

Normally we get the data displayed in the following manner :
Header1 Header2 Header3
Data Data Data
Data Data Data
But what if the requirement is to display it as below:
Header1 Data Data
Header2 Data Data
Header3 Data Data
just like covered in http://www.c-sharpcorner.com/UploadFile/sd_patel/DisplayVerticalData11242005011015AM/DisplayVerticalData.aspx
I want it should be done by Stored procedure. Plz. let me know how to do this?
Make sure performance would be also part of solution and should be covered.
Display results VERTICALLY in Results window
http://qa.sqlservercentral.com/scripts/Miscellaneous/31935/
should not cover if i have more than 10 rows, as described in Limitations

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Please provide sample data and table structure. Displaying data *vertically* is not normally an issue, as that's how tables are usually constructed in the first place. So we'll need to see how your data is laid out to see where the issue is. Please see the link in my signature on how to provide sample data that we can use, not just a jumble of text on the screen.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Greg Snidow SSCoach Points: 16106 More actions · Answer 2

Garadin (12/22/2008)
Please provide sample data and table structure. Displaying data *vertically* is not normally an issue, as that's how tables are usually constructed in the first place.

It seems like maybe he meant to ask for how to display data *horizontally*, in which case a cross tab might work.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 3

Here is a high level approach to doing it with dynamic SQL.

1. Get list of columns for the table in question.

select c.name, object_name(c.object_id) as tbl

from sys.columns c

join sys.objects o on c.object_id = o.object_id

where o.type = 'U'

and object_name(c.object_id) = 'yourTableName'

2. For each column, select the column name as a constant, and generate a delimited list of all values for that column.

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=CreateACommaDelimitedList&referringTitle=Home

3. For each delimited list, parse the delimited list into Col1 through Col10. Use the previously stored column name as the value of Col0.

http://qa.sqlservercentral.com/Forums/Topic601177-145-1.aspx

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 4

Thanks bob for you help and time, actually i did not get chance to see it working. Please make it more simple if possible. Here is my requirements in detail.

-- Create Table

CREATE TABLE tblInfo

( MemberID int IDENTITY,

First_Name varchar(50),

Last_Name varchar(50),

City varchar(50),

[State] varchar(50),

)

-- Load Sample Data

INSERT INTO tblInfo VALUES ('Marsha','Watson','Hines','IL')

INSERT INTO tblInfo VALUES ('Curley','Young','Alexandria','LA')

INSERT INTO tblInfo VALUES ('Keith','Robinson','Washington','DC')

INSERT INTO tblInfo VALUES ('Robert','Sakowski','New Orleans','LA')

INSERT INTO tblInfo VALUES ('Andrew','Trister','Philadelphia','PA')

select * from tblInfo

first_Namelast_Namecitystate

MarshaWatsonHinesIL

CurleyYoungAlexandriaLA

KeithRobinson WashingtonDC

RobertSakowski New OrleansLA

AndrewTristerPhiladelphiaPA

but i want results like following:

first_NameMarshaCurleyKeithRobertAndrew

last_NameWatsonYoungRobinsonSakowskiTrister

cityHinesAlexandriaWashingtonNew OrleansPhiladelphia

stateILLADCLAPA

Please help.

Shamshad Ali.

erdem1973 SSCommitted Points: 1843 More actions · Answer 5

Hello,

This is a cross-tab report format.You can also use PIVOT command in SQL Server 2005 environment.

Here is a link for you:

http://qa.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/

I hope it helps you

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 6

I'm not sure pivot/unpivot will solve this problem, but I've been wrong MANY times before.

How many unique member IDs will be in the actual table on production? There are limits to the number of columns we can create.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

John Rowan SSC Guru Points: 56440 More actions · Answer 7

Would you mind if I asked why you would want to do that anyway? Is this for a report? Are you planning to reference the value as a recordset in code?

If this is for a report, do this type of work in the reporting tool and let SQL Server just return the data to the tool.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden