How can show one Row in rows in which each row contain column name and the other contain the value?

I have a table that contain about 60 columns and if i write a select query in query analyser to get a row it show it and i must scroll to show data ..

How can i get one row in which it shows as 60 row, each row contain 2 columns one for column name and other for value

For example Select *开发者_开发百科 from table where id = 1 in default it shown as

ID Col1  Col2  Col3 Col4 Col5 ...... Col60  
1  v1    v2    v3   v4   v5   ...... v60

I want it to be shown as

ID    1  
Col1  v1  
Col2  v2  
Col3  v3
...  
Col60 v60

The below Stored Procedure do What you need

CREATE PROCEDURE [dbo].[Rotat]
    -- Add the parameters for the stored procedure here
     @Where nvarchar(max),
     @tableName nvarchar(max)
AS
BEGIN

Declare @SqlQuery nvarchar(max),@ColumnName nvarchar(255)
DECLARE @TempTable TABLE 
( 
      ID int IDENTITY(1,1) , 
      ColumnName nvarchar(255), 
      ColumnValue ntext 
)


INSERT INTO @TempTable (ColumnName)
    SELECT      column_name
    FROM information_schema.columns
    WHERE table_name = @tableName 

Declare @index int
Set @index = 1;  

Declare @Count int
Select @Count = Count(ID) from @TempTable
declare @columnValue  nvarchar(255)
declare @paraDef nvarchar(max)
declare @string nvarchar(max)

WHILE @index <= @Count 
BEGIN
    Select @ColumnName = ColumnName from @TempTable where id = @index
    set @string ='select @ret= cast('+ @ColumnName + ' AS nvarchar(255) )  
        from '+@tableName+' WHERE ' + @Where

    set  @paraDef=N'@ret nvarchar(255) output'

    EXECUTE sp_executesql @string, @paraDef,@ret=@ColumnValue  output

    UPDATE  @TempTable 
    SET     ColumnValue = @columnValue
    WHERE   ID =@index

    Set @index = @index + 1
END

Select * from @TempTable

END

Just call it an pass table name and your condition that will return one row for example

EXEC    [dbo].[Rotat]
        @Where = 'UserID = 123456',
        @tableName = 'Users'

If you want a very quick technique just for viewing data transposed (as your question seems to suggest), try copying and pasting the output from the results grid into Excel, then copy the Excel data and use Paste Special to Transpose the output.