MS SQL Server Data Loop

I need to loop though some data that gets the company...
Then adds the customers in a loop
Then gets the detail data in a loop

And builds "layered" data set

This is my "desired" output "look"
All data will be full left flush of course
CompanyStart   ('CompanyStart,' + Company)
   CustomerStart	('CustStart,' + Customer + ',' + CustomerAddress)
       Detail   ('Detail,' + FullName + ',' +  Amount)   
   CustomerEnd  ('CustEnd')
CompanyEnd  ('CompanyEnd')

Open in new window




Here is example data
DECLARE @table TABLE
    (
        Company VARCHAR(100) ,
        Customer VARCHAR(100) ,
        CustomerAddress VARCHAR(1000) ,
        FullName VARCHAR(100) ,
        Amount VARCHAR(100)
    );
INSERT INTO @table ( Company ,
                     Customer ,
					 CustomerAddress,
                     FullName ,
                     Amount )
VALUES ( 'MyCompany', 'ABC Company', '100 Main Street Anywhere Texas 12345', 'Tom Adams', '1.00' ) ,
       ( 'MyCompany', 'ABC Company', '100 Main Street Anywhere Texas 12345', 'Sarah Branch', '1.14' ) ,
       ( 'MyCompany', 'ABC Company', '100 Main Street Anywhere Texas 12345', 'Jim Smith', '.26' ) ,
       ( 'MyCompany', 'Wholesale Foods', '2301 65th Street Somewhere Texas 25689', 'Benjamin Derikson', '3.25' ) ,
       ( 'MyCompany', 'Wholesale Foods', '2301 65th Street Somewhere Texas 25689', 'Carley Huts', '10.25' );

SELECT * FROM @table

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well, here is my first foray and testing other possibilities -  including stripping the xml tags...

Also wondered about customer branches, so decided that a grouping on customeraddress was needed. Might need to consider another level.

But need some sleep preferably before I have to wake up :) So, decided to post a working version...

  Select company, min(customer) as customer,1 as lvl, 'CompanyStart,' + company as strData
  from #table group by company
  union all
  Select company, customer,2 as lvl,'  CustStart,' + Customer + ',' + CustomerAddress as strData
  from #table group by company,customer,CustomerAddress
  union all
  Select company, customer,3 as lvl, '    Detail,' + FullName + ',' +  convert (varchar(20),Amount) as strData
  from #table 
  union all
  Select company, customer,4 as lvl, '  CustEnd' as strData
  from #table group by company,customer,customerAddress
  union all
  Select company, max(customer),5 as lvl, 'CompanyEnd' as strData
  from #table group by company
  order by 1,2,3

Open in new window


Hopefully a more elegant solution to follow - the above was loosely inspired by xml explicit....
1
 
Mark WillsTopic AdvisorCommented:
So, it is an XML style hierarchy but without the XML -  is that a fair description ?

Will have a look - probably a few different ways to achieve - will be interesting to see the 'Answers' :)

And thank you for supplying a well constructed question including test data :)
0
 
Larry Bristersr. DeveloperAuthor Commented:
Key Mark...
Correct... xml style hierarchy without the xml tags

Thanks!
0
 
Larry Bristersr. DeveloperAuthor Commented:
Hey Mark...
I am testing but looks good so far!
0
 
Larry Bristersr. DeveloperAuthor Commented:
Great!
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.

All Courses

From novice to tech pro — start learning today.