convert the columns to row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

convert the columns to row

Author

Topic

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-05-27 : 03:22:16

hello everybody,
this is my current resultset:

SERVER_NAME DRIVE TOTAL_SIZE FREE_SPACE USED_SPACE
DC-JAVEEDAHMED\SQL C 49 12 37

i need to display the result set as this..please help me

SERVER_NAME-----DC-JAVEEDAHMED\SQL
DRIVE-----------C
TOTAL_SIZE------49
FREE_SPACE------12
USED_SPACE------37

the query i used is :
SELECT B.SERVER_NAME,A.DRIVE,C.TOTAL_SIZE,C.FREE_SPACE,C.USED_SPACE,
CONVERT(INT,(CONVERT(DECIMAL(18,2),USED_SPACE)/CONVERT(DECIMAL(18,2),TOTAL_SIZE))*100) PERCENT_SPACE_USED,
CONVERT(VARCHAR,CONVERT(DATETIME,A.PING_DATE),111) PING_DATE ,A.PING_TIME FROM
(
SELECT
SERVER_ID,
DRIVE,
CONVERT(VARCHAR,MAX(CONVERT(DATETIME,PING_DATE)),112) PING_DATE,
CONVERT(VARCHAR,MAX(CONVERT(DATETIME,PING_TIME)),108) PING_TIME
FROM DISK_PING_LOG
GROUP BY
SERVER_ID,
DRIVE
) A
JOIN SERVERS B
ON A.SERVER_ID=B.SERVER_ID
JOIN DISK_PING_LOG C
ON B.SERVER_ID=C.SERVER_ID
AND A.DRIVE=C.DRIVE
AND A.PING_DATE=C.PING_DATE
AND A.PING_TIME=C.PING_TIME

thanks in advance

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-27 : 05:26:33

Try this may help you

Select ServerName,UsedSpace from
(SELECT B.SERVER_NAME AS SERVER_NAME ,A.DRIVE as DRIVE,C.TOTAL_SIZE AS TOTAL_SIZE,C.FREE_SPACE as FREE_SPACE,C.USED_SPACE as USED_SPACE,
CONVERT(INT,(CONVERT(DECIMAL(18,2),USED_SPACE)/CONVERT(DECIMAL(18,2),TOTAL_SIZE))*100) PERCENT_SPACE_USED,
CONVERT(VARCHAR,CONVERT(DATETIME,A.PING_DATE),111) PING_DATE ,A.PING_TIME as PING_TIME FROM
(
SELECT
SERVER_ID,
DRIVE,
CONVERT(VARCHAR,MAX(CONVERT(DATETIME,PING_DATE)),112) PING_DATE,
CONVERT(VARCHAR,MAX(CONVERT(DATETIME,PING_TIME)),108) PING_TIME
FROM DISK_PING_LOG
GROUP BY
SERVER_ID,
DRIVE
) A
JOIN SERVERS B
ON A.SERVER_ID=B.SERVER_ID
JOIN DISK_PING_LOG C
ON B.SERVER_ID=C.SERVER_ID
AND A.DRIVE=C.DRIVE
AND A.PING_DATE=C.PING_DATE
AND A.PING_TIME=C.PING_TIME ) As UNPIVOT
(ServerName for UsedSpace IN (SERVER_NAME,DRIVE,TOTAL_SIZE,FREE_SPACE,USED_SPACE,PERCENT_SPACE_USED,PING_DATE,PING_TIME) ) AS unpvt

In Love... With Me!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-05-27 : 07:44:19

quote:
Originally posted by raghuveer125

Try this may help you

Select ServerName,UsedSpace from
(SELECT B.SERVER_NAME AS SERVER_NAME ,A.DRIVE as DRIVE,C.TOTAL_SIZE AS TOTAL_SIZE,C.FREE_SPACE as FREE_SPACE,C.USED_SPACE as USED_SPACE,
CONVERT(INT,(CONVERT(DECIMAL(18,2),USED_SPACE)/CONVERT(DECIMAL(18,2),TOTAL_SIZE))*100) PERCENT_SPACE_USED,
CONVERT(VARCHAR,CONVERT(DATETIME,A.PING_DATE),111) PING_DATE ,A.PING_TIME as PING_TIME FROM
(
SELECT
SERVER_ID,
DRIVE,
CONVERT(VARCHAR,MAX(CONVERT(DATETIME,PING_DATE)),112) PING_DATE,
CONVERT(VARCHAR,MAX(CONVERT(DATETIME,PING_TIME)),108) PING_TIME
FROM DISK_PING_LOG
GROUP BY
SERVER_ID,
DRIVE
) A
JOIN SERVERS B
ON A.SERVER_ID=B.SERVER_ID
JOIN DISK_PING_LOG C
ON B.SERVER_ID=C.SERVER_ID
AND A.DRIVE=C.DRIVE
AND A.PING_DATE=C.PING_DATE
AND A.PING_TIME=C.PING_TIME ) As UNPIVOT
(ServerName for UsedSpace IN (SERVER_NAME,DRIVE,TOTAL_SIZE,FREE_SPACE,USED_SPACE,PERCENT_SPACE_USED,PING_DATE,PING_TIME) ) AS unpvt

In Love... With Me!

thanx for the reply raghu..
but it is giving error incorrect syntax near for..
iam unable to trace it..
please help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources