April 13, 2012 at 2:51 pm
I have a table named as "querylog" which looks like follows with 3 columns. In the column "selectedAttributes", there are few delemeters(|) attributes.
I want to write a query to get the sum of all the attributes in that columns.
in this case it is 4+4+5+6 = 19 (abc has 4 attributes, xyx has 4 attributes, xyz has 5 attributes, abc has 6 attributes)
I guess i need a while loop here. I'm not that expert in TSQL.
QueryIDUserAlias SelectedAttributes
1 abc 4216,2|4215,2|4280,5|4282,3
2 xyz 4271,5|4094,3|4216,1|4215,1
3 xyz 4276,5|4216,2|4215,2|3964,2|4215,1
4 abc 4271,5|3994,3|4216,1|4215,1|4215,1|4215,1
how can I do this.
If I need further explaination please let me know.
April 13, 2012 at 3:09 pm
I do not think you need a loop to do this at all. If you can post ddl and sample data (insert statements) along with desired output based on your sample data you will find lots of people around here willing and able to help. Take a look at the first link in my signature for best practices on posting questions. You might want to take a look at the string splitter article as it most certainly will be needed to accomplish this.
_______________________________________________________________
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/
April 13, 2012 at 3:23 pm
April 13, 2012 at 3:26 pm
Gabe T. (4/13/2012)
You probably need a string splitter and an unpivot. let me see what I can come up with.
Look at the splitter in my signature.
Depending on some details from the OP this should be pretty simple.
_______________________________________________________________
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/
April 13, 2012 at 3:30 pm
April 13, 2012 at 3:37 pm
Mac1986, are you trying to aggregate the rows or the columns?
i.e are you trying to aggregate...
4216,2|4215,2|4280,5|4282,3
4271,5|4094,3|4216,1|4215,1
4276,5|4216,2|4215,2|3964,2|4215,1
4271,5|3994,3|4216,1|4215,1|4215,1|4215,1
or
4216,2|4215,2|4280,5|4282,3
4271,5|4094,3|4216,1|4215,1
4276,5|4216,2|4215,2|3964,2|4215,1
4271,5|3994,3|4216,1|4215,1|4215,1|4215,1
April 16, 2012 at 7:23 am
It sounds like you just want a count of the attributes. If that is the case, you don't need the string splitter.
SELECT SUM(LEN(SelectedAttributes) + 1 - LEN(REPLACE(SelectedAttributes, '|', ''))
FROM QueryLog
This essentially calculates the number of delimiters in the field and then sums all of those results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply