October 24, 2006 at 7:46 am
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
October 24, 2006 at 8:32 am
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
October 24, 2006 at 8:35 am
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
-
October 24, 2006 at 9:12 am
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