Database

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)

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)

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

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)

Kschuler wrote:

how to convert an integer into a string in a select statement

DECLARE @dtVar DATETIME
SELECT @dtVar = CONVERT(VARCHAR, GETDATE(),101)
PRINT @dtVar

Rob Philpott wrote:

I've never quite got the hang of the date conversion functions with the arbitary number (101) denoting something.