SQL Server - converting rows into columns with out pivot

hi all
i have a table like this

id      item
--------------
1       a
2       b
3       c
4       d
5       e
6       f
7       g


i want this to be

1    2    3    4    5   6   7
---------------------------
a    b    c   d    e   f   g


i tried using pivot...but in pivot i cant do this with out aggreagate.....
because i cant use aggreagate here how can i do this
please advice me

here just i want to tranform the rows in to column


thanks in advance
MUTHU

Follow this example-






 return results like this:

Here is SQL Query:
First, Create table called ashish..

CREATE TABLE [dbo].[ashish](
    [custID] [int] NULL,
    [Question] [varchar](50) NULL,
    [answer] [varchar](20) NULL
) ON [PRIMARY]
 

Then, insert some values..

INSERT INTO ashish values(1000,    'AAA',    '1')
INSERT INTO ashish values(1000,    'BBB B',    '2')
INSERT INTO ashish values(1000,    'CCC',    '3')
INSERT INTO ashish values(1001,    'AAA',    '2')
INSERT INTO ashish values(1001,    'BBB B',    '3')
INSERT INTO ashish values(1001,    'CCC',    '3')
INSERT INTO ashish values(1000,    'DDD',    '6')

here is pivot query to convert rows to columns..

Declare @t VARCHAR(10)
Declare @A VARCHAR(1000)
Declare @B VARCHAR(1000)
set @A='SELECT CustID'
SET @B='('
SELECT @A=@A+',['+Question+'] as [' +Question+']',@B=@B+'['+Question+'],' FROM (SELECT DISTINCT QUESTION FROM ashish) cur
-- removing last ',' from both variables
SET @B=SUBSTRING(@B,1,LEN(@B)-1)
SET @A=@A+ + ' FROM (SELECT CustID, Answer, Question FROM ashish) s  PIVOT (max(answer) FOR Question IN ' +@B+')) p ORDER BY [CUSTID]; '
exec(@A);

no in my case i cant apply any aggreagate functions because
all my columns are varchar
i have only columns and n number of rows i want to just transform  these rows into columns and columns in tp rows ...no other calculations there



thanks in advance
MUTHU

select max(case when ID = 1 then ID end) as [1], max(case when ID = 2 then ID end) as [2], etc. from myTable

Just have a look on http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7a8d6c35-01fd-4b47-9a52-9b856c3a7824/

I think it is exactly same with which you have required

hi,

You can accomplish this by a "pivot" query. Please look at the small testcase that I prepared below:

SQL> desc t1
 Name Null? Type

 NAME VARCHAR2(10)
 YEAR NUMBER(4)
 VALUE NUMBER(4)

SQL>
SQL> select * from t1;

NAME YEAR VALUE
---------- ---------- ----------
john 1991 1000
john 1992 2000
john 1993 3000
jack 1991 1500
jack 1992 1200
jack 1993 1340
mary 1991 1250
mary 1992 2323
mary 1993 8700
9 rows selected.

SQL> -- now, try out the pivot query
SQL> select year,
  2 max( decode( name, 'john', value, null ) ) "JOHN",
  3 max( decode( name, 'jack', value, null ) ) "JACK",
  4 max( decode( name, 'mary', value, null ) ) "MARY"
  5 from
  6 (
  7 select name, year, value
  8 from t1
  9 )
 10 group by year ;

  YEAR JOHN JACK MARY
---------- ---------- ---------- ----------
  1991 1000 1500 1250
  1992 2000 1200 2323
  1993 3000 1340 8700

SQL>

Hope this will help you