November 28, 2008 at 2:42 am
Hello All,
I have a table IssueMst with following structure with issueid as primary key
issueid status ownedby desc
1A1 xyz
2A1 xyz
41A43 xyz
56A34 xyz
57I45 ABC
58Itr ABC
59Irt ABC
i want to display the result set like
desc status ownedby desc status ownedby
xyzA1 ABC I 45
xyzA1 ABC I tr
xyzA43 ABC I rt
xyzA34 null null null
ie. based on status i want to repeat the same columns horizontally in the resultset ,can anybody please help me out!
November 28, 2008 at 2:50 am
Looks like you need to join the table with itself. But from the example you give I cannot understand what your join condition should look like.
Please give more information which objects you want to combine.
November 28, 2008 at 3:02 am
this is the sample schema and query
create table #Temp
(
issueid int not null,
status nvarchar(1),
ownedby nvarchar(15),
[desc] nvarchar(max)
)
insert into #Temp values(1,'A','1','xyz')
insert into #Temp values(2,'A','1','xyz')
insert into #Temp values(41,'A','43','xyz')
insert into #Temp values(56,'A','34','xyz')
insert into #Temp values(57,'I','45','ABC')
insert into #Temp values(58,'I','tr','ABC')
insert into #Temp values(59,'I','rt','ABC')
select * from #Temp
--drop table #Temp
(select t1.status,t1.ownedby from #Temp t1
where t1.status='A')
(select t2.status,t2.ownedby from #Temp t2
where t2.status='I')
two result sets are returned by the above query .. i want to merge the result sets vertically to display in single result set
November 28, 2008 at 5:26 am
This should do the trick:
[font="Courier New"]DROP TABLE #IssueMst
CREATE TABLE #IssueMst (issueid INT, status CHAR(1), ownedby VARCHAR(2), [desc] VARCHAR(3))
INSERT INTO #IssueMst (issueid, status, ownedby, [desc])
SELECT 1, 'A', '1', 'xyz' UNION ALL
SELECT 2, 'A', '1', 'xyz' UNION ALL
SELECT 41, 'A', '43', 'xyz' UNION ALL
SELECT 56, 'A', '34', 'xyz' UNION ALL
SELECT 57, 'I', '45', 'ABC' UNION ALL
SELECT 58, 'I', 'tr', 'ABC' UNION ALL
SELECT 59, 'I', 'rt', 'ABC'
;WITH IssueMst AS (
SELECT ROW_NUMBER() OVER (PARTITION BY [status] ORDER BY issueid) AS theRow, *
FROM #IssueMst)
SELECT a.[desc], a.status, a.ownedby, i.[desc], i.status, i.ownedby
FROM IssueMst a
FULL OUTER JOIN IssueMst i ON i.theRow = a.theRow AND i.status = 'I'
WHERE a.status = 'A'
[/font]
Results:
desc status ownedby desc status ownedby
xyz A 1 ABC I 45
xyz A 1 ABC I tr
xyz A 43 ABC I rt
xyz A 34 NULL NULL NULL
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply