July 7, 2008 at 6:31 am
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.
July 7, 2008 at 7:57 am
Can you send me some sample records and your table structure.
And whats your expected result.
July 7, 2008 at 8:18 am
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.
PROD QA DEV
ProdServer1 QAServer1 DEVServer1
ProdServer2 QAServer2 DEVServer2
ProdServer3 QAServer3 DEVServer3
ProdServer4 QAServer4 DEVServer4
Is something like this possible?
July 7, 2008 at 11:58 am
Shameless bump ... I tried a pivot, didn't get what I was looking for.
Is what I'm attempting to do possible?
Thanks all
July 7, 2008 at 12:40 pm
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
July 7, 2008 at 1:11 pm
Ugly but it does what you want 😉
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 [PROD], [QA], [DEV]
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY Environment ORDER BY ServerName) n, * FROM @ServerNames
) a
PIVOT ( min(ServerName) FOR Environment IN ([PROD], [QA], [DEV]) ) b
* Noel
July 7, 2008 at 1:24 pm
Nice! Thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply