October 28, 2015 at 10:11 am
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
October 28, 2015 at 10:26 am
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.
October 28, 2015 at 10:38 am
Hi,
I have now attached how it looks like and how I would like it to look like.
Thanks again
Celine
October 28, 2015 at 10:48 am
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.
October 28, 2015 at 10:48 am
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.
October 28, 2015 at 10:56 am
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
October 28, 2015 at 10:57 am
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 🙂
October 28, 2015 at 11:00 am
@ 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
October 28, 2015 at 11:21 am
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
October 28, 2015 at 11:25 am
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.
October 29, 2015 at 3:33 am
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 😉
October 29, 2015 at 4:05 am
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.
October 29, 2015 at 4:31 am
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 🙂
October 29, 2015 at 4:43 am
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.
October 29, 2015 at 4:53 am
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 16 total)
You must be logged in to reply to this topic. Login to reply