Note that there are only 2 reids in table 1 and 2. I want combine information of reid1 and reid2 for a account in one single row. Any suggestions on how to achieve this.
Assuming that acct is your unique identifier and that you have a one-to-many relationship with your read(x) fields (meaning that each acct can have more than one read1 or read2 field associated with each acct record). For proper database normalization you
will need a bridge table to handle that one to many relationship (or many to many). Assuming your database structure is below....
declare @acct int
set @acct = 170529
select t.acct,t.premise,t.reid as ReId1,t.[read] as [Read1],t1.reid as ReId2,t1.read as [Read2] from
(SELECT acct,premise,reid,read where reid=1 and acct=@acct)t
inner join (SELECT acct,premise,reid,read where reid=2 and acct=@acct)t1 on t.acct = t1.acct and t.premise = t1.premise
get the ReId = 1 records into one result set and Reid = 2 records into another resultset... Use both as subqueries and inner join them.
see the example.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
quest3297
Member
99 Points
106 Posts
Combine rows from two queries
Jul 12, 2010 08:57 PM|LINK
I have plain sql query as shown below
SELECT acct,premise,reid,read where reid=@reid and acct=@acct
when i pass reid=1 and acct=170529 i get rows as
acct premise reid read
175029 1750 1 264
175029 1750 1 88
175029 1750 1 20
when i pass reid=2 and acct=170529 i get rows as
acct premise reid read
175029 1750 2 43
175029 1750 2 90
175029 1750 2 96
Now i want to modify the query as such when i pass reid= -1 and acct=170529 i should get the result set as
acct premise reid1 reid2 read1 read2
175029 1750 1 2 264 43
175029 1750 1 2 88 90
175029 1750 1 2 20 96
Note that there are only 2 reids in table 1 and 2. I want combine information of reid1 and reid2 for a account in one single row. Any suggestions on how to achieve this.
DAmanding
Member
488 Points
142 Posts
Re: Combine rows from two queries
Jul 13, 2010 12:37 AM|LINK
Assuming that acct is your unique identifier and that you have a one-to-many relationship with your read(x) fields (meaning that each acct can have more than one read1 or read2 field associated with each acct record). For proper database normalization you will need a bridge table to handle that one to many relationship (or many to many). Assuming your database structure is below....
Select Table1.acct, Table1.premise, Table1.reid1, Table2.acct, Table2.reid2, BridgeTable.acct, BridgeTable.read from Table1 INNER JOIN (Table2 INNER JOIN BridgeTable on Table2.acct = BridgeTable.acct) ON Table1.acct = BridgeTable.acct
Inner Joins drive me nuts so the above may not be perfect. Google inner joins for better detail.
Edit: Grr on the code section being split. Assume it's all one section.
SQL
Newbie just trying to pass on what I've recently learned myself. :)
jsd24
Participant
1380 Points
255 Posts
Re: Combine rows from two queries
Jul 13, 2010 04:36 AM|LINK
Use Pivot in your select statement
refer this link
http://jsdideas.blogspot.com/2010/01/pivot-in-multipal-column.html
If not geting idea let me know so i can help you
My Blog : http://jsdideas.blogspot.com
ramireddyind...
All-Star
31358 Points
4579 Posts
Re: Combine rows from two queries
Jul 13, 2010 05:19 AM|LINK
get the ReId = 1 records into one result set and Reid = 2 records into another resultset... Use both as subqueries and inner join them.
see the example.
quest3297
Member
99 Points
106 Posts
Re: Combine rows from two queries
Jul 13, 2010 02:40 PM|LINK
This is returning me 9 rows instead of 3
acct premise reid1 reid2 read1 read2
175029 1750 1 2 264 43
175029 1750 1 2 88 43
175029 1750 1 2 20 43
175029 1750 1 2 264 90
175029 1750 1 2 88 90
175029 1750 1 2 20 90
175029 1750 1 2 264 96
175029 1750 1 2 88 96
175029 1750 1 2 20 96
Any idea on what iam missing.
DAmanding
Member
488 Points
142 Posts
Re: Combine rows from two queries
Jul 13, 2010 03:57 PM|LINK
Select Distinct
See here: http://www.sql-tutorial.com/sql-distinct-sql-tutorial/
Newbie just trying to pass on what I've recently learned myself. :)
quest3297
Member
99 Points
106 Posts
Re: Combine rows from two queries
Jul 13, 2010 04:26 PM|LINK
All the rows are distinct. Please see the row values.
brijeshdsing...
Member
128 Points
38 Posts
Re: Combine rows from two queries
Jul 13, 2010 04:30 PM|LINK
can u send ur table structure and some exmple data.
Brijesh Kumar Singh
brijeshdsingh@gmail.com
Don't forget to mark as answer, if it helps
quest3297
Member
99 Points
106 Posts
Re: Combine rows from two queries
Jul 13, 2010 06:04 PM|LINK
Please find table definition and data, sql query to insert data.
create
table pleasesupplydata(acct int, premise int, reid int, [read] int)INSERT
INTO pleasesupplydatavalues
(175029, 1750, 1, 264 )INSERT
INTO pleasesupplydatavalues
(175029, 1750, 1, 88 )INSERT
INTO pleasesupplydatavalues
(175029, 1750, 1, 20 )INSERT
INTO pleasesupplydatavalues
(175029, 1750, 2, 43 )INSERT
INTO pleasesupplydatavalues
(175029, 1750, 2, 90 )INSERT
INTO pleasesupplydatavalues
(175029, 1750, 2, 96)SELECT
t1.acct, t1.premise, reid1, reid2, read1, read2FROM
( SELECT acct, premise, reid as reid1, [read] as read1FROM pleasesupplydatawhere reid = 1and acct = 175029)
t1INNER
JOIN ( SELECT acct, premise, reid as reid2, [read] as read2FROM pleasesupplydatawhere reid = 2and acct = 175029)
t2On
t1.acct = t2.acctAnd
t1.premise = t2.premise;jsd24
Participant
1380 Points
255 Posts
Re: Combine rows from two queries
Jul 14, 2010 04:57 AM|LINK
DECLARE @pleasesupplydata TABLE ( acct INT, premise INT, reid INT, [read] INT) INSERT INTO @pleasesupplydata values (175029, 1750, 1, 264 ) INSERT INTO @pleasesupplydata values (175029, 1750, 1, 88 ) INSERT INTO @pleasesupplydata values (175029, 1750, 1, 20 ) INSERT INTO @pleasesupplydata values (175029, 1750, 2, 43 ) INSERT INTO @pleasesupplydata values (175029, 1750, 2, 90 ) INSERT INTO @pleasesupplydata values (175029, 1750, 2, 96) SELECT acct , premise , SUM([reid1]) AS reid1 , SUM([reid2]) AS reid2 , SUM([reid3]) AS reid3 , SUM([read1]) AS read1 , SUM([read2]) AS read2 , SUM([read3]) AS read3 FROM ( SELECT acct , premise , ROW_NUMBER() OVER (PARTITION BY reid order by acct, premise) AS DispRank , 'reid' + CAST( reid AS VARCHAR(10) ) AS reidCd , reid , 'read' + CAST( reid AS VARCHAR(10) ) AS readCd , [read] FROM @pleasesupplydata ) AS OrderDetail PIVOT ( SUM(reid) FOR reidCd IN ( [reid1], [reid2], [reid3] ) ) AS pvtReidCd PIVOT ( SUM([read]) FOR readCd IN ( [read1], [read2], [read3] ) ) AS pvtReadCd GROUP BY acct, premise, DispRank ORDER BY acct, premise, DispRankMy Blog : http://jsdideas.blogspot.com