December 16, 2003 at 10:46 am
I have a table that looks like this:
SubID PubID SubjectText
1486281594714Domestic fiction.
1486282594714Psychological
1486302594717Historical fiction.
1486303594717Bildungsromane.
I need to produce a table that looks like this:
PubID SubjectText1 SubjectText2
594714 Domestic fiction. Psychological
594717 Historical fiction. Bildungsromane.
Can anyone help me out?
December 16, 2003 at 11:11 am
Assuming that you have only 2 Subject Texts for each PubID:
select PubID, min(SubjectText), max(SubjectText)
From TableA
Group by PubID
December 16, 2003 at 11:35 am
Might try something like this:
set nocount on
create table books(SubID int, PubID int, SubjectText varchar(30))
insert into books values(1486281, 594714, 'Domestic fiction.')
insert into books values(1486282, 594714, 'Psychological')
insert into books values(1486302, 594717, 'Historical fiction.')
insert into books values(1486303, 594717, 'Bildungsromane')
create table ordered_books (PubID int, SubjectText1 varchar(30), SubjectText2 varchar(30))
select * from books
declare @p char(1000)
declare @i int
declare @max-2 int
declare @m int
declare @cmd varchar(1000)
set @p = ''
select @max-2=max(distinct pubid) from books
-- set @m to the first id number
select top 1 @m = pubid from books order by pubid
-- Process until no more items
begin
-- string together all items with a comma between
select @i = pubid, @p = rtrim(@p) + ', '''+ subjecttext + ''''
from books a
where pubid = @m
-- print detail row
set @cmd = 'insert into ordered_books values (' + cast(@i as varchar(10))+ ' ,' + rtrim(substring(@p,3,len(@p))) + ')'
print @cmd
exec(@cmd)
-- increment id number
select top 1 @m = pubid from books
where pubid > @m order by pubid
set @p = ''
end
select * from ordered_books
drop table books
drop table ordered_books
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 16, 2003 at 12:10 pm
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply