Data formatted differently than in table ?? 2

[link9]

Hello all --

I couldn't come up with a good subject for this question, so excuse it, please. ;-)

Here's the deal:

Data:
1234 Joe
1234 Sue
1234 Bob
1234 Jon

What I'd like my output to look like:
1234 Joe Sue Bob Jon

Can I do this with SQL? What would the statement look like if I can?

Thanks for any input!
Paul Prewett

 

[link9]

I take this as a no? I suspected this, so can someone confirm or deny this for me?

Thanks,
paul

 

[foxdev]

I can't think of a single statement that would do this kind of pivot; using a cursor and a loop, yes, but that wouldn't be advisable. -----------------
Robert Bradley
use coupon code "TEKTIPS" for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com

 

[link9]

Thanks, Robert.

 

[tlbroadbent]

What follows is not a single statement but a script that performs a pivot action on a table. The script utilizes a temporary table and creates dynamic SQL so that it can handle any number of entries in the 2nd column of your data set.
[tt]
Input table: Table1
RecID int (1234 in your example)
RecName char(6) (the names)

The script:

--Create a temporary table to hld pivot items and counter
Create table #tmp (RecID int, RecName char(6), RecNo int)

--Load temp table
Insert Into #tmp (RecID, RecName, RecNo)
Select
RecId, RecName,

--Increment counter for each occurrence of RecNmae per RecID
(Select count(*) From #t
Where RecId=t.RecId And RecName<=t.RecName)

From Table1 As t
Order By RecId, RecName

--Declare variables needed to build dynamic SQL
Declare @recno int, @sql nvarchar(4000), @lc int

--Find largest RecNo in temp table
Select @recno=max(RecNo) From #tmp

--Begin building SQL statement
Set @sql='Select RecId'

--Initialize loop count
Set @lc=1
While @recno>0
Begin
Set @sql=@sql + ', ' + char(10) +
' max(Case RecNo When ' +
ltrim(str(@lc)) +
' Then RecName Else '''' End) As Col' +
ltrim(str(@lc))
Set @lc=@lc+1
Set @recno=@recno-1
End

--Complete SQL statement
Set @sql=@sql +
char(10) +
'From #tmp Group By RecId' +
char(10)

--Optional print of dynamic SQL statement
--Print @sql

--Execute SQL statement
Exec(@sql)

--Cleanup
Drop table #tmp[/tt] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mukund]

Hi link9,
Below is a query which can be a probable solution.
Ofcourse this qry. assumes that there are only 4 people.
I mean this can work where you know that the records that need to be represented from rows to columns are known.

In the qry given below, ColA holds value '1234'
and Colb holds values 'Joe', 'Sue', 'Bob', 'Jon'

select distinct ColA,
(Select Distinct a.Colb from Tests a, Tests b where a.Colb = 'Joe' and a.Cola = b.Cola ) as A,
(Select Distinct a.Colb from Tests a, Tests b where a.Colb = 'Sue' and a.Cola = b.Cola ) as B,
(Select Distinct a.Colb from Tests a, Tests b where a.Colb = 'Bob' and a.Cola = b.Cola ) as C,
(Select Distinct a.Colb from Tests a, Tests b where a.Colb = 'Jon' and a.Cola = b.Cola ) as D,
from tests



Hope this helps,
Mukund.