March 10, 2003 at 1:52 pm
Hi, I have a table where each record contains the following info:
masterid,GeoState,GeoCity.
Example masterid=67, GeoState=CA, GeoCity = Los Angeles.
I am concatenating the Columns to read CA#Los Angeles# for import into another system.
The issue is I have multiple records for each ID, and I want to create a concatentation for the entire recordset :
masterid=67, GeoState=CA, GeoCity = Los Angeles
masterid=67, GeoState=CA, GeoCity = San Diego
masterid=67, GeoState=CO, GeoCity = Denver
Would yield the result:
CA#Los Angeles#CA#San Diego#CO#Denver#
Is this possible using SQL?
I guess basically I am trying to concatenate Rows/Columns into one Row/Colum where they share a common Identifier.
Thanks---
March 10, 2003 at 2:06 pm
You might try something like this:
set nocount on
create table Ttest (masterid int, GeoState char(2), GeoCity char(40))
insert into Ttest values (67,'CA', 'Los Angeles')
insert into Ttest values (67,'CA', 'San Diego')
insert into Ttest values (67,'CA', 'Denver')
insert into Ttest values (68,'WA', 'Redmond')
insert into Ttest values (68,'OR', 'Portland')
declare @p char(1000)
set @p = ''
select @p = rtrim(@p) + rtrim(GeoState) + '#'+ rtrim(GeoCity) + '#'
from Ttest
where masterid = 67
print @p
drop table Ttest
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
March 10, 2003 at 2:43 pm
You could quickly run into variable size limitations doing this in T-SQL if your data comes back with any significant number of rows. If you are using ADO you could use the RecordSet.GetString method as an alternative. I used Greg's table script to create the table for the view.
TSQL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwTtest]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwTtest]
GO
CREATE view vwTtest
AS
SELECT RTRIM(GeoState) + '#'+ RTRIM(GeoCity) + '#' As Data
FROM Ttest
GO
The VB Code:
Dim adoRs As ADODB.Recordset
Set adoRs = New ADODB.Recordset
adoRs.Open "SELECT DISTINCT Data FROM vwTtest", mstrConn, adOpenForwardOnly, adLockReadOnly, adCmdText
Debug.Print adoRs.GetString(ColumnDelimeter:="", RowDelimeter:="#", NullExpr:="NULL")
Tim C.
//Will write code for food
Tim C //Will code for food
March 10, 2003 at 3:03 pm
The big whoosh you heard was all of those suggestions going over my head.
What I can grasp is that I am basically creating a temp table, then populating it with the values I need to Concatenate.
However I don't quite see this code as possible inside a view?
I was attempting to use a While...End statement ala WARNING bad CODE ahead...
Select masterid as ID
While Id = masterid
Select CombinedField + Select(CombinedField AS GeoState + '#' + GeoCity + '#')
End
Essentially I want to store the record where the masterid is the same as one record...
I about to put a fork in this turkey....
March 10, 2003 at 3:35 pm
How are you doing the exporting?
Tim C.
//Will write code for food
Tim C //Will code for food
March 10, 2003 at 4:03 pm
Maybe I wasn't clear enough.
This is part (subquery) of a large roll up query. Essentially what I need to determine is if it's possible to accomplish this task.
Here is some raw data:
masterid GeoState GeoCity
-------- -------- -------
67 CA Los Angeles
67 CA San Diego
67 CA Sacramento
67 CA Bakersfield
67 CA Barstow
67 NV Las Vegas
67 CO Grand Junction
67 CO Vail
Now the table does have an index however I have excluded for discussion purposes. Basically this table allows someone to add as many or few records as they need, which are linked (One to many) this table being many to another tblCompanyInfo.
I need to take this information and create an Excel spreadsheet based on the database information. Basically the application it is going into parses an excel spread sheet and the Geographic regions need to be ONE FIELD with the # sign as the delimiter separating State, City etc.
Example: XL Output
Company Name | Geographic Work Area
ABC Company | #CA#Los Angeles#CA#San Diego#...CO#Denver#...ETC.
This application basic displays the data in a Databound grid control in ASP.NET which when sorted can dynamically create an Excel Sheet.
I am calling either a query or a stored proc to display the data.
Whew...
I am not a seasoned pro, I can write a mean stored proc or query but I may have bitten off a little too muh this time. Thanks for everyones input.
March 13, 2003 at 3:11 am
If I understand it, you want to denormalise the data from many rows into a single string.
This might work:
declare @concat_string varchar(8000), @masterid int, @GeoState char(2), @GeoCity varchar(50), @prev_masterid int
/* Initialise variables */
set @prev_masterid = 0
set @concat_string = ''
declare concat_cursor cursor for
select masterid, GeoState, GeoCity
from <tablename>
order by masterid
open concat_cursor
fetch next from concat_cursor into @masterid, @GeoState, @GeoCity
while @@fetch_status = 0 begin
/* Check for change of masterid */
if @@prev_masterid <> @masterid begin
/* If the string is not empty then output - prevents output first time through */
if @concat_string > '' print @concat_string /* or other output statement */
/* Set @prev_masterid to current value of @masterid so that can do comparison next time through */
set @prev_masterid = @masterid
/* Reset concat_string to blank */
set @concat_string = ''
end
/* Concatenate GeoState and GeoCity to string */
set @concat_string = @concat_string + '#' + @GeoState + '#' + @GeoCity
/* Get the next row from the cursor */
fetch next from concat_cursor into @masterid, @GeoState, @GeoCity
end
/* Output final row */
print @concat_string
close concat_cursor
deallocate concat_cursor
This uses a cursor to scroll through the rows of the table so you can concatenate the rows into a string. There is a test in the loop to output the string on change of masterid which then resets the concatenation string.
Also, for the last value of masterid, you have to force it to output the string as the test within the loop does not catch the last value as there is no next record.
I know that there are some people who do not like using cursors but I find them invaluable when you need to record level processing as in this case.
I haven't checked the syntax so apologies if it is not right.
Jeremy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply