• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Need a view that merges multiple rows of another view, based on Ord_no

I have a view in MSSQL 2005 that contains up to 6 rows of information per order (shown below).  Up to 3 sales persons, and up to 3 external sales persons (max) can be found in the view.
I need to collapse all of that down to a view that only shows 1 row per order, and spreads the sales people information out across multiple, pre-defined, columns.

I have been playing around with variations on unions, stuffs, selects etc. from previous posts here, but with no luck.  Any help would be very appreciated.

Starting view
 Starting View
 Ending View
0
westtexman
Asked:
westtexman
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can do like this:

select Ord_No

   , max(case when rn = 1 then SlsPsn end) slspsn1
   , max(case when rn = 1 then Pct end) pct1
   , max(case when rn = 1 then Ext_SlsPsn end) SlsPsn1

   , max(case when rn = 2 then SlsPsn end) slspsn2
   , max(case when rn = 2 then Pct end) pct2
   , max(case when rn = 2 then Ext_SlsPsn end) SlsPsn2
  -- etc ---
 from  ( select t.*, row_number() over (partition by ord_no order by Ord_no) rn
   from yourtable t
   ) sq
group by ord_no 

Open in new window

0
 
westtexmanAuthor Commented:
Here's my variation on that, just to keep it simple and play with just the 4 fields you mention.....  it crashes my Microsoft Sql Mgmt Studio software on my client pc with a buffer overrun.  But thanks for the suggestion.

select Ord_No
   , max(case when rn = 1 then SlsPsn end) slspsn1
   , max(case when rn = 1 then Pct end) pct1
   , max(case when rn = 1 then Ext_SlsPsn end) SlsPsn1

   , max(case when rn = 2 then SlsPsn end) slspsn2
   , max(case when rn = 2 then Pct end) pct2
   , max(case when rn = 2 then Ext_SlsPsn end) SlsPsn2

 from  ( select t.*, row_number() over (partition by ord_no order by Ord_no) rn
   from Sales_Com t
   ) sq
group by ord_no

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Microsoft Sql Mgmt Studio software on my client pc
outch ... what version?

works just fine for me.

you surely have a index on ord_no ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
westtexmanAuthor Commented:
Sql Mgmt Studio is 9.00.5000

Sales_Com is simply a view that is a union of 6 other views.  It was built by a consultant a few years ago.  When I open it in design, I don't see anything to indicate an index.  Can views be indexed the same way that normal sql tables can?   Here's the sql behind this view, which reads the other views, which in turn read from a table.  I was trying to not have to recreate the sql between the table and the view in question.....:)   As bad as I am with SQL, I can't claim this stack of ...code... !   Thanks again for your suggestions and help.

{Sales_Com table design}
SELECT     *
FROM         slspsn1
UNION
SELECT     *
FROM         slspsn2
UNION
SELECT     *
FROM         slspsn3
UNION
SELECT     *
FROM         slspsn4
UNION
SELECT     *
FROM         slspsn5
UNION
SELECT     *
FROM         slspsn6

{slspsn1 table design}
SELECT     STUFF('00000000', 9 - LEN(CAST(ord_no AS int)), LEN(CAST(ord_no AS int)), CAST(ord_no AS int)) AS ord_no, SalesPerson1 AS slspsn,
                      SalesPerson1Pct AS pct, ROUND(SalesPerson1RigidPct, 2) AS rgd_pct, '' AS ext_Slspsn, 0 AS ext_pct, 0 AS ext_rgd_pct
FROM         dbo.BOL_SOtoSPCommision
WHERE     (SalesPerson1 <> '')
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Sales_Com is simply a view that is a union of 6 other views.
I fear that is the underlying problem ...
0
 
westtexmanAuthor Commented:
The question is being abandoned.  There is no good solution and the question needs to be re-evaluated and posed a different way next time.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now