November 2, 2011 at 11:10 am
I have a table as follows in sql 2005
CustID - ProductName - IndexValue1 - IndexValue2 - IndexValue3
1 Product A 34 55 98
1 Product B 55 44 67
1 Product C 43 19 41
2 Product A 49 3 6
2 Product B 90 3 47
2 Product C 18 24 82
2 Product D 50 12 29
I would like to flip the data so that I have 1 row per "CustID" with column headers as the ProductName and the index value (1-3) as the next columns in a sql statement so I can grab this as a dataset in vb.net.
The final query result should look like this
CustID - ProductA_IndexValue1, ProductB_IndexValue2, ProductC_IndexValue3
1 34 55 98
Thanks,
Derek
November 2, 2011 at 11:15 am
That is really not what sql does. It is possible but it is difficult to pull of because it breaks 1st normal form. Check out this article from Jeff Moden on dynamic cross tabs. http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]
My suggestion would be to do this in .NET instead of sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 2, 2011 at 11:21 am
Okay, here are some questions on this:
1. How does the database know which product comes first, which one second, et al, or does that not matter?
2. You have three products on your first customer ID, and four on the second. How many columns of products do you want in your dataset? Dynamic based on number of rows? And will your .NET dataset know how to deal with different numbers of columns each time it's instantiated?
3. What are "Index Values" and how do they relate to the products? Why are they stored as what looks like a repeating characteristic in a row, instead of being normalized into rows vertically?
Can't help much without knowing those things. Once you clarify those, I can probably help you get what you're asking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 11:40 am
GSquared (11/2/2011)
Okay, here are some questions on this:1. How does the database know which product comes first, which one second, et al, or does that not matter?
Reply: product order doesn't matter will reference the column name by the productname in the final dataset
2. You have three products on your first customer ID, and four on the second. How many columns of products do you want in your dataset? Dynamic based on number of rows? And will your .NET dataset know how to deal with different numbers of columns each time it's instantiated?
Reply: currently 14 distinct products, will always be the same for each CustID
3. What are "Index Values" and how do they relate to the products? Why are they stored as what looks like a repeating characteristic in a row, instead of being normalized into rows vertically?
Reply: this is the way the data is being provided...no choice to change....my mistake the final dataset should look like this:
CustID - ProductA_IndexValue1 - ProductA_IndexValue2 - ProductA_IndexValue3, (then Product B, Product C ect)
there would be a max of 14 products per custid I would need 14 sets of the "IndexValue" columns in the same row per custid.
Index values are sales indexes by city, province, country per custid.
Can't help much without knowing those things. Once you clarify those, I can probably help you get what you're asking for.
November 2, 2011 at 12:05 pm
Okay, so the dataset should be 1 column for the customer ID, and 14X3 (42) columns for the products. Got that.
Got it on the data being provided as-is. Had to ask.
Easiest way to do this will be a query of customer IDs from a customers table (if you have one) or a distinct query of customer IDs in the provided data if you don't have one, then Outer Apply each product.
select ID
from dbo.Customers
Outer Apply (select IndexValue1 as ProductAIndex1, IndexValue2 as ProductAIndex2, IndexValue3 as ProductAIndex3
from dbo.MyImportedTable
where MyImportedTable.CustomerID = Customers.ID
and ProductName = 'Product A') as ProdA
Outer Apply (select IndexValue1 as ProductBIndex1, IndexValue2 as ProductBIndex2, IndexValue3 as ProductBIndex3
from dbo.MyImportedTable
where MyImportedTable.CustomerID = Customers.ID
and ProductName = 'Product B') as ProdB
--...copy and paste 12 more times, change column names and sub-query alias and Where clause.
With liberal application of copy and paste, should just take a few minutes to slap this together.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 1:39 pm
This may be a little more automated for you 🙂
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(CustID varchar(1),ProductName varchar(50),IndexValue1 int, IndexValue2 int, IndexValue3 int)
go
INSERT INTO #mytable(CustID, ProductName, IndexValue1, IndexValue2, IndexValue3)
SELECT '1', 'Product A',34,55,98 UNION ALL
SELECT '1', 'Product B',55,44,67 UNION ALL
SELECT '1', 'Product C',42,19,41 UNION ALL
SELECT '2', 'Product A',49,3,6 UNION ALL
SELECT '2', 'Product B',90,3,47 UNION ALL
SELECT '2', 'Product C',18,24,82
--original data
select * from #mytable
--build string
DECLARE @prodnames varchar(max)
SELECT top 1 @prodnames=(SELECT '(select IndexValue1 from #mytable where ProductName='''+
ProductName+''' and CustID = m.CustID) as '+replace(ProductName,' ','')+'Index1,(select IndexValue2 from #mytable where ProductName='''+
ProductName+''' and CustID = m.CustID) as '+replace(ProductName,' ','')+'Index2,(select IndexValue3 from #mytable where ProductName='''+
ProductName+''' and CustID = m.CustID) as '+replace(ProductName,' ','')+'Index3,'
from #mytable m
WHERE CustID = a.CustID
ORDER BY CustID FOR xml path(''))
FROM #mytable a
--remove last comma
SET @prodnames = LEFT(@prodnames, LEN(@prodnames) - 1)
--execute dynamic sql
declare @sql varchar(max)
set @sql = 'select CustID,'+@prodnames+' from #mytable m group by m.CustID'
exec(@sql)
Result:
CustID ProductAIndex1 ProductAIndex2 ProductAIndex3 ProductBIndex1 ProductBIndex2 ProductBIndex3 ProductCIndex1 ProductCIndex2 ProductCIndex3
------ -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- --------------
1 34 55 98 55 44 67 42 19 41
2 49 3 6 90 3 47 18 24 82
(2 row(s) affected)
_________________________________
seth delconte
http://sqlkeys.com
November 2, 2011 at 3:57 pm
Thanks for you reply I tried running this query as you have it changing the table and field names as necessary and it only ever returns the first column in the top query (CustID). It doesn't seem to want to display the columns in the "outer apply" select query.
I think I may be missing something.....been thinking too long today......
November 2, 2011 at 4:13 pm
derek.waltho (11/2/2011)
Thanks for you reply I tried running this query as you have it changing the table and field names as necessary and it only ever returns the first column in the top query (CustID). It doesn't seem to want to display the columns in the "outer apply" select query.I think I may be missing something.....been thinking too long today......
Sorry this reply was to GSquared's post.....
November 2, 2011 at 4:32 pm
Wouldn't a pivot table be able to achieve that?
I'm trying to make an example but I'm not being successful because I'm not experience in using that technique.
Perhaps someone more experienced may be able to do so.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
November 2, 2011 at 4:51 pm
codebyo (11/2/2011)
Wouldn't a pivot table be able to achieve that?I'm trying to make an example but I'm not being successful because I'm not experience in using that technique.
Perhaps someone more experienced may be able to do so.
Best regards,
That is what the reference (By Sean) for the dynamic cross tabs reference was trying to achieve.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 2, 2011 at 5:29 pm
SQLRNNR (11/2/2011)
codebyo (11/2/2011)
Wouldn't a pivot table be able to achieve that?I'm trying to make an example but I'm not being successful because I'm not experience in using that technique.
Perhaps someone more experienced may be able to do so.
Best regards,
That is what the reference (By Sean) for the dynamic cross tabs reference was trying to achieve.
I read the post but I didn't check the reference. :blush:
That's an excellent article that deserves some study. Thank you.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
November 3, 2011 at 6:19 am
derek.waltho (11/2/2011)
Thanks for you reply I tried running this query as you have it changing the table and field names as necessary and it only ever returns the first column in the top query (CustID). It doesn't seem to want to display the columns in the "outer apply" select query.I think I may be missing something.....been thinking too long today......
Add the columns to the outer Select. I left it with just ID in the Select clause, but you'll need to add the ones you want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2011 at 11:20 am
Awesome Thanks GSquared!!! this worked out perfectly once I read your post and implemented it properly.
Thanks Again!
Derek
November 10, 2011 at 11:26 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply