• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • 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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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