concatenate values using an UPDATE

  • Consider the following detail and summary files.  I want to get a result in tbl_summary that looks like this:  (in other words, concatenate the county names together to get one field)

    WV     Kanawha Putnam Dodridge Rhone

    OH      Buckeye Kings Island Hamilton

    but instead I get this result:

    WV     Rhone

    OH      Hamilton

    Can you tell me what's wrong with my UPDATE?

    Thanks

    Rick

     

     

    create table tbl_detail

    (

    state  char(2),

    county  varchar(20)

    )

    insert into tbl_detail values('WV', 'Kanawha')

    insert into tbl_detail values('WV', 'Putnam')

    insert into tbl_detail values('WV', 'Dodridge')

    insert into tbl_detail values('WV', 'Rhone')

    insert into tbl_detail values('OH', 'Buckeye')

    insert into tbl_detail values('OH', 'Kings Island')

    insert into tbl_detail values('OH', 'Hamilton')

    select *

    from tbl_detail

    select state, convert(varchar(100),'') as county_list

    into tbl_summary

    from tbl_detail

    group by state

    select *

    from tbl_summary

    select a.state, b.county

    from tbl_summary a

    inner join tbl_detail b on a.state = b.state

    update tbl_summary

    set county_list = county_list + b.county + ' '

    from tbl_summary a

    inner join tbl_detail b on a.state = b.state

    select *

    from tbl_summary

  • I hope you are looking for this

    Give a try

    DECLARE @tbl TABLE

     (RowId  VARCHAR(20),

     RValue  VARCHAR(30),

     Lineage  VARCHAR(3000) DEFAULT '',

     Depth  INT)

     

    insert into @tbl(RowId,RValue)  values('WV', 'Kanawha')

    insert into @tbl(RowId,RValue)  values('WV', 'Putnam')

    insert into @tbl(RowId,RValue)  values('WV', 'Dodridge')

    insert into @tbl(RowId,RValue)  values('WV', 'Rhone')

    insert into @tbl(RowId,RValue)  values('OH', 'Buckeye')

    insert into @tbl(RowId,RValue)  values('OH', 'Kings Island')

    insert into @tbl(RowId,RValue)  values('OH', 'Hamilton')

    DECLARE @sql VARCHAR(3000)

    DECLARE @RowId VARCHAR(20)

    DECLARE @val VARCHAR(3000)

    DECLARE @Cnt INT

    SELECT @Val = ''

    UPDATE Source

    SET @RowId  = CASE WHEN @RowId IS NULL THEN Source.RowId ELSE @RowId END,

     @Cnt = CASE WHEN @Cnt IS NULL THEN 0 ELSE @Cnt END,

     @Val = Lineage = CASE WHEN @RowId = Source.RowId THEN @val+ISNULL(Source.RValue,'NULL')+',' ELSE ISNULL(Source.RValue,'NULL')+',' END,

     @Cnt = Depth = CASE WHEN @RowId = Source.RowId THEN @Cnt+1 ELSE NULL END,

     @RowId = Source.RowId

    FROM @tbl AS Source

    SELECT RowId,

     SUBSTRING(MAX(Lineage),1,LEN(MAX(Lineage))-1) AS Lineage,

     COUNT(1) AS NoOfCounts

    FROM  @tbl

    GROUP BY RowId

    Ram

     

     

     

  • What you want to do is a pivot with two columns of data.  SQL 2005 has a pivot function, search BOL for with pivot and pivot etc. 

    If you are using 2000 you can apply the following.  Note: this sample below uses integers, it is a posting that I responded to several months ago.  you will need to apply your data types to this.  (see original post: Transforming table rows to columns )

    DECLARE @iCostCount INT

    DECLARE @iMaxColumns INT

    DECLARE @iColumn INT

    DECLARE @iRowId INT

    DECLARE @iRowId2 INT

    DECLARE @iIdCount INT

    DECLARE @iCostId INT

    DECLARE @rCost REAL

    DECLARE @vcExecSQL VARCHAR(8000)

    DECLARE @vcInsertString VARCHAR(8000)

    DECLARE @tblCosts TABLE (iRowId INT IDENTITY(1,1),iCostId INT, iCostCount INT)

    DECLARE @tblInsertData TABLE (iRowId INT IDENTITY(1,1),rCost REAL)

    DECLARE @iFirst INT

    INSERT INTO @tblCosts Select distinct iCostId,count(*) from tblPivotData group by iCostId Order by iCostId DESC

    SET @iRowId = @@ROWCOUNT

    SET @iMaxColumns = (Select max(iCostCount) from @tblCosts)

    SET @iColumn = 1

    SET @vcExecSQL = 'IF EXISTS(Select * from sysobjects where id = OBJECT_ID(N''tblPivotedData''))'

      +' DROP TABLE tblPivotedData'

    EXEC (@vcExecSQL)

    SET @vcExecSQL = 'CREATE TABLE tblPivotedData(iCostId INT NOT NULL,'

    WHILE @iColumn <= @iMaxColumns

    BEGIN

     IF @iColumn = @iMaxColumns

      SET @vcExecSQL = @vcExecSQL+'rCost'+cast(@iColumn as varchar)+' REAL CONSTRAINT DF_rCost_'+cast(@iColumn as varchar)+' DEFAULT (0))'

     ELSE

      SET @vcExecSQL = @vcExecSQL+'rCost'+cast(@iColumn as varchar)+' REAL CONSTRAINT DF_rCost_'+cast(@iColumn as varchar)+' DEFAULT (0),'

     SET @iColumn = @iColumn + 1

    END

    EXEC (@vcExecSQL)

    DECLARE @iColCount INT

    DECLARE @i INT

    WHILE @iRowId > 0

    BEGIN

     SET @iCostId = (Select iCostId from @tblCosts where iRowId = @iRowId)

     INSERT INTO @tblInsertData Select rCost from tblPivotData where iCostId = @iCostId

     SET @iColCount = @@ROWCOUNT

     SET @iRowId2 = (Select max(iRowId) from @tblInsertData)

     SET @iFirst = 1

     

     SET @vcInsertString = 'INSERT INTO tblPivotedData (iCostId'

     WHILE @iColCount > 0

     BEGIN

      SET @vcInsertString = @vcInsertString+',rCost'+cast(@iColCount as varchar)

      SET @iColCount = @iColCount-1

     END

     SET @vcInsertString = @vcInsertString+') SELECT '+cast(@iCostId as varchar)+','

     

     WHILE @iRowId2 >= (select min(iRowId) from @tblInsertData)

     BEGIN

      SET @rCost = (Select rCost from @tblInsertData where iRowId = @iRowId2)

      IF @iFirst = 1

      BEGIN

       SET @vcInsertString = @vcInsertString+' '+cast(@rCost as varchar)

       SET @iFirst = 0

      END

      ELSE

       SET @vcInsertString = @vcInsertString+','+cast(@rCost as varchar)

      SET @iRowId2 = @iRowId2-1

     END

     EXEC (@vcInsertString)

     delete from @tblInsertData

     SET @iRowId = @iRowId-1 

    END

    Select * from tblPivotedData

    -

  •  

    Thanks for the quick response.  Both replies were very helpful.

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

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