How to get multiples rows and columns data into 1 single row - Help please?

  • I have the following results:

    CustomerProductName ValueChargesNewCharges

    13AZ 40005056

    13BY 30023

    13BX 50003536

    13BW 16001312

    13BV 107009392

    13BU 7000109110

    And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.

    Could anyone help or am I being too picky?

    Thank you so much in advance

  • celine.godines (10/28/2015)


    I have the following results:

    CustomerProductName ValueChargesNewCharges

    13AZ 40005056

    13BY 30023

    13BX 50003536

    13BW 16001312

    13BV 107009392

    13BU 7000109110

    And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.

    Could anyone help or am I being too picky?

    Thank you so much in advance

    Based on the data provided, can you show how the desired output would look, please?

    Can you also confirm that the output column definitions will always be exactly the same for all of the data which you are querying? It's not possible to have a mix of columns returned from a query.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi,

    I have now attached how it looks like and how I would like it to look like.

    Thanks again

    Celine

  • OK, I understand. But will there be the same number of columns for every customer, or will the number of columns vary depending on the number of products for each customer?

    Also, will you be producing this data for several customers at a time, or just one customer at a time?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • celine.godines (10/28/2015)


    ...

    And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.

    Could anyone help or am I being too picky?

    Thank you so much in advance

    Wat if some customer has 3 products, will you want three columns for products and more for other values?

    Will your query always be for one single customer? If not, what will happen if different customers will have different number of products?

    You can use cross-tab or pivot query to do it. They will need to be dynamic if number of products/values can be different.

    Is it how it should be displayed on screen/UI or it is just for extract purposes? If the last, then you can just concatinate values as strings using FOR XML PATH method.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • celine.godines (10/28/2015)


    I have the following results:

    CustomerProductName ValueChargesNewCharges

    13AZ 40005056

    13BY 30023

    13BX 50003536

    13BW 16001312

    13BV 107009392

    13BU 7000109110

    And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.

    Could anyone help or am I being too picky?

    Thank you so much in advance

    as per previous replies...this could get ugly quick.

    perhaps if you could explain who is going to use this and how they intend to use it....we may be able to provie a better way.

    this request seems to me to come from a "spreadsheet" user.......what happens when the number of products/charges etc increase.....does the end user really want a never ending list of columns?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • It will be for multiple customers (I have about over 1000000)

    All customers have different products and different Names/Values... (and not always 2 products and 6 names, it will varies)

    I have never done excel macro linked with SQL that's why I am trying to get all the details in 1 row for each customer so I can use Excel formulas in each tab of my worksheet to get the right data populated when I enter the customer number.

    I know that I am making my work life more complicated than it should be but couldn't find any other way to get all my tabs populated with the right data unless I get that 1 row result for each of my customers.

    Again, thank you guys for taking the time to have a look at this 🙂

  • @ J Livingston SQL

    Yes it is from a spreadsheet and not a solution per se, only need to get the data in the spreadsheet sorted so when it works I can work out a way with a developer or else to get a macro sorted to link everything to the right database and get the live results instead.

    hope this make sense.

    Thank you

  • celine.godines (10/28/2015)


    It will be for multiple customers (I have about over 1000000)

    All customers have different products and different Names/Values... (and not always 2 products and 6 names, it will varies)

    I have never done excel macro linked with SQL that's why I am trying to get all the details in 1 row for each customer so I can use Excel formulas in each tab of my worksheet to get the right data populated when I enter the customer number.

    I know that I am making my work life more complicated than it should be but couldn't find any other way to get all my tabs populated with the right data unless I get that 1 row result for each of my customers.

    Again, thank you guys for taking the time to have a look at this 🙂

    why is it necessary to have this data in excel?

    what is excel doing that SQL cant?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • celine.godines (10/28/2015)


    It will be for multiple customers (I have about over 1000000)

    All customers have different products and different Names/Values... (and not always 2 products and 6 names, it will varies)

    I have never done excel macro linked with SQL that's why I am trying to get all the details in 1 row for each customer so I can use Excel formulas in each tab of my worksheet to get the right data populated when I enter the customer number.

    I know that I am making my work life more complicated than it should be but couldn't find any other way to get all my tabs populated with the right data unless I get that 1 row result for each of my customers.

    Again, thank you guys for taking the time to have a look at this 🙂

    So in your spreadsheet, you 'enter a customer number' and then get 'some data populated'.

    Perhaps you can describe what that data is?

    The varying number of columns is not easily handled in SQL. The best you will be able to do (and there's some challenging code to be written to do it) is to write some dynamic SQL which analyses your data, finds the maximum number of columns and then outputs all data with those columns.

    But perhaps the data can be summarised somehow first, in order to give you the data you want.

    Or Excel may be able to give you what you need based on your existing resultset ... you just don't know how yet.

    It all depends on what you need it to do.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Morning!!

    I am doing some data gathering before 'the real deal', I am not a wiz on SQL, manage my way around but that's pretty much it, hence the use of excel at first so I can export the data from SQL into Excel and work out some formulas to my tabs are populated.

    I have a cell where I enter the customer number and the spreadsheets I have in each tab are filled with formulas (IFS, VLOOKUPS....) so when I enter the customer numbers I have my 'Summary' tab filled with totals of products, before and after charges..., in my 'Charges details' tab I have name of different products with details of the charges. That kind of thing.

    It worked with a customer with 1 product, 1 name, 1 charge but I am having a hard time getting the customers with multiple products/charges/values pulled.

    Happy Thursday everyone and once again, thank you all for you help 😉

  • celine.godines (10/29/2015)


    Morning!!

    I am doing some data gathering before 'the real deal', I am not a wiz on SQL, manage my way around but that's pretty much it, hence the use of excel at first so I can export the data from SQL into Excel and work out some formulas to my tabs are populated.

    I have a cell where I enter the customer number and the spreadsheets I have in each tab are filled with formulas (IFS, VLOOKUPS....) so when I enter the customer numbers I have my 'Summary' tab filled with totals of products, before and after charges..., in my 'Charges details' tab I have name of different products with details of the charges. That kind of thing.

    It worked with a customer with 1 product, 1 name, 1 charge but I am having a hard time getting the customers with multiple products/charges/values pulled.

    Happy Thursday everyone and once again, thank you all for you help 😉

    It's nice that you have taken the time to thank the people who contribute here. Thank you for that.

    Maybe you can change the problem round a little?

    What about the possibility of the user selecting a customer number in Excel and then Excel firing some queries at SQL Server to get the required info dynamically, there and then?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I thought about this but I wouldn't know how to start to be honest. I am a system tester since February only, I am getting better with SQL queries but not to the point where I would know how to get a macro kind of thing in Excel to gather information from SQL. It'll be the easiest way if I knew what I was doing ;-)!

    I am very thankful to everyone who took or will take the time to get back to me to help me out with this. No one has to do this hence all my thank yous, I'm very grateful for all the messages pointing me to the right direction 🙂

  • celine.godines (10/29/2015)


    I thought about this but I wouldn't know how to start to be honest. I am a system tester since February only, I am getting better with SQL queries but not to the point where I would know how to get a macro kind of thing in Excel to gather information from SQL. It'll be the easiest way if I knew what I was doing ;-)!

    I am very thankful to everyone who took or will take the time to get back to me to help me out with this. No one has to do this hence all my thank yous, I'm very grateful for all the messages pointing me to the right direction 🙂

    Once you get to the point where you can submit a query along the lines of

    "select (columns) from (table) where CustomerId = (selected customerId)"

    from Excel and display the results, people here can help you refine those queries to get exactly the data you want out of your source system.

    But the very basic 'plumbing' stuff is down to you. It's not difficult & there is plenty of reference material freely available.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Sounds great :-D!

    Would it too cheeky to ask if you could point me to the right direction to get my head around the basic 'plumbing' stuff so my next query in the forum is more accurate?

    Thanks again Phil, really appreciated your help

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply