Formating data

Question

Formating data

Kabir Patel

Grasshopper

Points: 14
More actions
November 21, 2006 at 4:44 am

#173510

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Now way around that with the grid result.

Also if you are using access to report this data, you can use the group by functions to "hide" the duplicates. So a simple select would be required from sql server.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

Nothing a little self join correlated subquery magic can't fix... works in the grid or text modes...

--===== 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

...and the kicker is, if you setup the text output to use a tab delimiter, it makes something you can copy and paste directly into a spreadsheet INCLUDING the column headers...

The real key is... why are you using a mere spreadsheet to do the work of a database?

"Before you think outside the box, consider the box you're in..." --Jeff Moden circa 2004

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)