Crosstab Query - Strings - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Crosstab Query - Strings

Author

Topic

cindylee
Yak Posting Veteran

55 Posts

Posted - 2005-12-14 : 22:47:52

Hi everyone
Want to generate a Cross tab report

Example:

CREATE TABLE Pivot
( custid SMALLINT,
phone INT,
Amount DECIMAL(2,1),
[name] nvarchar(50),
call nvarchar(50) )
GO
INSERT INTO Pivot VALUES (1, 0097866, 1.1, 'kk','c0x44')
INSERT INTO Pivot VALUES (1, 0097866, 1.2,'yy','c09jjj')
INSERT INTO Pivot VALUES (1, 0097866, 1.3,'zz','kjdkj')
INSERT INTO Pivot VALUES (1, 0097866, 1.4,'xuoi','c0988zz')
INSERT INTO Pivot VALUES (2, 0566999, 2.1,'text','c0x909')
INSERT INTO Pivot VALUES (2, 0566999, 2.2,'bext','c0xxxko')
INSERT INTO Pivot VALUES (2, 0566999, 2.3,'xezt','so22po')
INSERT INTO Pivot VALUES (2, 0566999, 2.4,'xaezt','cpxopo00')
GO

Is there anyway to get the result without using cursor in the following format, iam stuck converting the column strings to row strings

Custid Phone price namecall
------ ------ ----- -------
1 0097866 5.0 kk-c0x44;yy-c09jjj;zz-kjdkj;xuoi-c0988zz
2 0566999 9.0 text-c0x909;bext-c0xxxko;xezt-so22po;xaezt-cpxopo00

Any help much appreciated

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-14 : 23:07:31

see the FAQ on Combined Rows into column or CrossTab at [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210[/url]

-----------------
[KH]
Learn something new everyday