Query to "Transform" a Table? - 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

SQL Server Development (2000)

Query to "Transform" a Table?

Author

Topic

phoenix22
Starting Member

20 Posts

Posted - 2004-08-06 : 22:42:15

Hi,

I have a table "Circuits" that has two columns (CircuitID & Location) and entries like the following:

CircuitID Location
1234 SF
1234 DC
1234 NJ
9876 NY
9876 TO

Is there a way I could create a query that groups the circuit ID and appends all the locations together, like below:

CircuitID Location
1234 SFDCNJ
9876 NYTO

Thanks in advance for your time and help.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-07 : 00:13:25

[code]
create table Circuits (CircuitID INT, Location char(2))

Insert Circuits Select 1234, 'SF'
Insert Circuits Select 1234, 'DC'
Insert Circuits Select 1234, 'NJ'
Insert Circuits Select 9876, 'NY'
Insert Circuits Select 9876, 'TO'

Create Function f_CircuitList (@CircuitID int)
Returns Varchar(2000) AS
BEGIN
DECLARE @CircuitList Varchar(2000)
SELECT @CircuitList = coalesce(@CircuitList,'') + Location from Circuits where CircuitID = @CircuitID
RETURN @CircuitList
END

Select CircuitID, dbo.f_CircuitList(CircuitID) Location From Circuits Group by CircuitID

CircuitID Location
----------- ----------
1234 SFDCNJ
9876 NYTO

[/code]

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

phoenix22
Starting Member

20 Posts

Posted - 2004-08-09 : 11:04:17

Thanks very much for your help Ken! Unfortunately I'm still having a problem. The table containing the data is already in my database and is called 'CircuitsWithCLLI'adnd thus I have the following in MS SQL Query Analyzer:
------------------------------------
Create Function f_CircuitList (@CIRCUIT_ID Varchar(255))
Returns Varchar(2000) AS
BEGIN
DECLARE @CircuitList Varchar(2000)
SELECT @CircuitList = coalesce(@CircuitList,'') + CLLI from CircuitsWithCLLI where CircuitsWithCLLI.CIRCUIT_ID = @CIRCUIT_ID
RETURN @CircuitList
END

Select CircuitsWithCLLI.CIRCUIT_ID, dbo.f_CircuitList(CIRCUIT_ID) CLLI From CircuitsWithCLLI Group by CIRCUIT_ID;

--------------------------
However, I keep getting an error message stating "Incorrect Syntax near keyword 'Select'; I've tried doing various modifications, but I'm still getting the same error message.

Any advice/suggestions would be much appreciated, thanks in advance.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-09 : 13:41:56

It looks fine to me. You need to run the statements seperatly. Create the function first then run the SELECT statement.

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources