Formatting Data - 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)

Formatting Data

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2006-11-21 : 06:38:22

I have data in a table as follows:

PersonID CarID
---------------------------
Person1 BlueCar
---------------------------
Person1 GreenCar
---------------------------
Person1 YellowCar
---------------------------
Person2 RedCar
---------------------------
Person2 BlueCar

I have been asked to output this data into the following format in excel:

PersonID CarID
---------------------------
Person1 BlueCar
GreenCar
YellowCar
---------------------------
Person2 RedCar
BlueCar

To do this I created a UDF that returns all the CarID's for a PersonID.

i.e. It is as follows:

CREATE FUNCTION dbo.Cars
(
@PersonID VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @CarID VARCHAR(8000)
SELECT @CarID = ISNULL(@CarID + char(13) + ' ', '') + CarID
FROM dbo.myTable
WHERE PersonID = @PersonID
RETURN @CarID
END

Then I query this function as follows:

SELECT
PersonID
,PersonTitle
,dbo.GetRoleNo(rtrim(PersonID))
FROM
(
SELECT
PersonID
,PersonTitle
FROM
myTable
GROUP BY
PersonID ,PersonTitle
) i

This works fine when I set query analyser to "results in text" mode but ignores the char(13) when I try "results in grid".

Is there any way around this?

Cheers,
Kabir

PS I am outputting the data via some VBA in an Access .adp using

DoCmd.OutputTo acOutputStoredProcedure, "dbo.myStoredProc", acFormatXLS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 06:40:56

No.
The RESULTS IN GRID truncates the result for a column when encountering the first newline or carriage return character.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-21 : 12:01:16

Cant you format in EXCEL itself?

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-01 : 22:48:17

Did the same thing for this same guy on another forum... thought I'd post it here...


--===== Supress the auto-display of rowcounts for appearance
    SET NOCOUNT ON

--===== Setup the test data... not part of the solution
DECLARE @Data TABLE (PersonID VARCHAR(10), CarID VARCHAR(10))
 INSERT INTO @Data (PersonID, CarID)
 SELECT 'Person1','BlueCar' UNION ALL
 SELECT 'Person1','GreenCar' UNION ALL
 SELECT 'Person1','YellowCar' UNION ALL
 SELECT 'Person2','RedCar' UNION ALL
 SELECT 'Person2','BlueCar'

--===== Demo the solution
 SELECT CASE 
            WHEN d2.CarID > (SELECT MIN(CarID) FROM @Data d1 WHERE d1.PersonID = d2.PersonID)
            THEN ''
            ELSE d2.PersonID
        END AS [Person ID],
        d2.CarID AS [Car ID]
   FROM @Data d2
  ORDER BY d2.PersonID, d2.CarID

--Jeff Moden

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources