-- Is it possible??? --

WetRivrRat

Hey guys, need to format a column of results to display as columns in another query.... let me explain a little better *hopefully* in the result set below notice how the empID is repeated in some cases

ID empID phone_num displayTel type

     159        ** 625** 123-123-4567          1           1
     163         **625** 465-456-4657          1           1
     154         626 944-789-4515          1           1
     155         626 984-321-4567          1           2
     150         639 984-266-3336          1           1
     145         **802** 465-465-7894          1           5
     146         **802** 654-798-4657          1           6
     165         853 132-123-1324          1           1

i need to beable to separate the "phone_num" data based on the "type" and then display that data in-line on another query i.e. Not This!!!

Name empID phone_num activeUser contact

Demo User ** 625** 123-123-4567 1 1
Demo User 625 465-456-4657 1 1

Instead Like This!!

Name empID phone_num1 phone_num2 activeUser contact

Demo User ** 625** 123-123-4567 465-456-4657 1 1

I need to be able to do this all at once while i'm also quering about 15 other tables.... so i need to be able to do it in-line or subqueried... my only idea would be to do something like this ('course it won't work)) SELECT col1, col2, (if (select phone_num from phone where empID = @empIDNum) > 1 { for i = 1 to (select count(phone_num)) select top (i) phone_num as phone_num+(i) from Phone where empID = @empIDNum Next} ) else { select phone_num from phone where empID = @empIDNum } from employees thanks!!! string Beautiful; Beautiful = "ignorant"; label1.Text = "The world is full of " + Beautiful +" people."; Why is common sense such an un-common comodity?

Eric Dahlvang

Can you use a stored procedure? As far as I know, you cannot do a cross-tab sql statement in SQL Server. There are ways to do it in a stored procedure though. ---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-Rollin

Michael Potter

Alias your phone table and join to employees twice. Use a left join so you are sure to get every employee even if they don't have the certain type of phone number. SELECT Name, empID, a.phone_num as phone_num1, b.phone_num as phone_num2, activeUser, contact FROM employees LEFT JOIN phone a ON (employees.empID = a.EmpID AND a.type = 1) LEFT JOIN phone b ON (employees.empID = b.EmpID AND b.type = 2) You can also use a correlated sub query. This method won't be as fast as the first method. SELECT Name, empID, (SELECT TOP phone_num FROM Phone WHERE empId = employees.empId AND type = 1) as phone_num1, (SELECT TOP phone_num FROM Phone WHERE empId = employees.empId AND type = 2) as phone_num2, activeUser, contact FROM employees -- modified at 14:52 Thursday 25th May, 2006

WetRivrRat

EricDv, Yes I can use a SP, my ultimate goal out of all this is to setup a dataset, for select and update string Beautiful; Beautiful = "ignorant"; label1.Text = "The world is full of " + Beautiful +" people."; Why is common sense such an un-common comodity?

WetRivrRat

Michael, Thank you for your reply, I find your soultion of using the multiple joins interesting... however I have a question on it, the possiblity exist that there will be an unnumerable amount of phone_num results available for each user. (while the truth is we "attempt" to limit it to 4) How would you propose doing the multiple joins for x number of results? My inital thought would be to use a while loop to loop thru all the results. but doing that still leaves the question of how to deal with the multiple results of the intial query... for instance when you do a left join on these tables, because there are say 4 instances of a phone_num in the phone table per user, then my list of users (say 100) will return with 400 results. even if i do the multilple joins on the same table i'll still have 400 results from the inital query, since the join doesn't consume the results in any way. --unless there is a way to include the TOP statement.... hmm, i'll look further into that.... If I figure that out then I'll post my resolution. -- 'course if you already know the resolution I'll happly accept assistance :laugh: string Beautiful; Beautiful = "ignorant"; label1.Text = "The world is full of " + Beautiful +" people."; Why is common sense such an un-common comodity?

Eric Dahlvang

There's probably a better way, but I had fun with this. It isn't pretty and I don't know how it will perform, - but here is one solution:

DECLARE @iColumns INT, @iCounter INT, @sql VARCHAR(2500) 

SET @iCounter = 1
SET @sql = ''

select @iColumns = (SELECT  TOP 1 COUNT(*) AS cnt FROM phonenumbers GROUP BY empid ORDER BY cnt DESC)

WHILE (@iCounter <= @iColumns)
BEGIN
	SET @sql = @sql + ', Phone' + CAST(@iCounter AS VARCHAR) + ' VARCHAR(12)'
	SET @iCounter = @iCounter + 1
END

SET @sql =  'CREATE TABLE #CrossPhones (empid INT' + @sql + ');'
 +  'DECLARE @sql2 VARCHAR(100),@iCounter INT,@iEmpIDHolder INT, @iEmpID INT, @phone_num VARCHAR(12), @teltype INT;'
 +  'DECLARE curNumbers CURSOR FOR SELECT empid,phone_num,teltype FROM phonenumbers p ORDER BY empid,teltype;'
 +  'OPEN curNumbers;'
 +  'FETCH NEXT FROM curNumbers INTO @iEmpID, @phone_num, @teltype;'
 +  'WHILE @@FETCH_STATUS = 0'
 +  'BEGIN'
 +  '   SET @iEmpIDHolder = @iEmpID;'
 +  '   INSERT INTO #CrossPhones (empid) VALUES (@iEmpID);'
 +  '   SET @iCounter = 0;'
 +  '   WHILE @iEmpIDHolder = @iEmpID'
 +  '   BEGIN'
 +  '	 SET @iCounter = @iCounter + 1;'
 +  '	 SET @sql2 = ''UPDATE #CrossPhones SET Phone'' + CAST(@iCounter AS VARCHAR) + ''='''''' + CAST(@phone_num AS VARCHAR) + '''''' WHERE empid='' + cast(@iEmpID as VARCHAR);'
 +  '     EXEC(@sql2);'
 +  '	 FETCH NEXT FROM curNumbers INTO @iEmpID, @phone_num, @teltype;'
 +  '	 IF @@FETCH_STATUS != 0 BREAK;'
 +  '   END;'
 +  'END;'
 +  'CLOSE curNumbers;'
 +  'DEALLOCATE curNumbers;'
 +  'SELECT e.name,c.* FROM #CrossPhones c INNER JOIN Employees e ON c.empid = e.empid;'

EXEC(@sql)

---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-Rollin

Eric Dahlvang

Here is another solution, but with this no employee can have two of the same phone type:

DECLARE @iColumns INT, @sql VARCHAR(2500),@cTypeID VARCHAR(10) 

SET @sql = ''

DECLARE curTypes CURSOR FOR SELECT  DISTINCT CAST(teltype AS VARCHAR(10)) FROM PhoneNumbers
OPEN curTypes
FETCH NEXT FROM curTypes INTO @cTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = @sql + ', (SELECT phone_num FROM PhoneNumbers p WHERE p.empid = e.empid AND p.teltype = ' + @cTypeID + ') as Type' + @cTypeID
	FETCH NEXT FROM curTypes INTO @cTypeID
END

close  curTypes
DEALLOCATE curTypes

SET @sql =  'SELECT e.name,e.empid' + @sql + ' FROM employees e'
exec(@sql)

---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-Rollin