Change look of resultset ... pivot?

Question

Post reply

Change look of resultset ... pivot?

Adam Bean

One Orange Chip

Points: 26631
More actions
July 7, 2008 at 6:31 am

#209511

Is it possibly to simply pivot to change the way a result set looks? In by that, I have two columns … ServerName and Environment. Basically I want the environment to be my column headers, per environment type, with their corresponding servernames displayed underneath.
SELECT DISTINCT
[ServerName]
,[Environment]
FROM [ServerAudit_DriveUsage_vw]
ORDER BY 2
Is such a thing possible? Is Pivot the right operation to be looking at?
Thanks.

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

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

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 1

Can you send me some sample records and your table structure.

And whats your expected result.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Here is an example ...

DECLARE @ServerNames TABLE

(

[ServerName]VARCHAR(50)

,[Environment]VARCHAR(50)

)

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('ProdServer1','PROD')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('ProdServer21','PROD')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('ProdServer3','PROD')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('ProdServer4','PROD')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('QAServer1','QA')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('QAServer2','QA')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('QAServer3','QA')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('QAServer4','QA')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('DEVServer1','DEV')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('DEVServer2','DEV')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('DEVServer3','DEV')

INSERT INTO @ServerNames ([ServerName],[Environment]) VALUES ('DEVServer4','DEV')

SELECT * FROM @ServerNames

I want to turn the environment column in the header ... so in this case, there would be 3 column headers - PROD, QA, DEV. Each of which would just show the servers that belong to that environment.

PRODQADEV

ProdServer1QAServer1DEVServer1

ProdServer2QAServer2DEVServer2

ProdServer3QAServer3DEVServer3

ProdServer4QAServer4DEVServer4

Is something like this possible?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Shameless bump ... I tried a pivot, didn't get what I was looking for.

Is what I'm attempting to do possible?

Thanks all

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 4

Hi,

You can;t do this with PIVOT table. You need to define the mapping table between the environemnts then only you can do this.

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

noeld SSC Guru Points: 96590 More actions · Answer 5

Ugly but it does what you want 😉

DECLARE @ServerNames TABLE

(

[ServerName] VARCHAR(50)

,[Environment] VARCHAR(50)