question

mcsc avatar image
mcsc asked

Compare Columns in the same row

Sorry I am a newbie....here is my data (see image) ....I need to compare multiple columns in the same row. I need to find how many columns (from A0 to A6) have the same value and what is this value. I tried with coalesce but it is not working....any idea?![alt text][1] [1]: /storage/temp/2915-esempio.png
sql
esempio.png (23.3 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
If I understand your question correctly, you want a list of values for each Id and how frequently those values occur for the Id. That is what the following code is designed to do. If that is not what you want, please explain and include a sample of desired output for the input you provided. Updated based on your comments. CREATE TABLE #unpivot (Id int, ColumnName char(2), ColumnValue char(1)); CREATE TABLE #ValueFrequency (Id int, ColumnValue char(1), Frequency int); INSERT #unpivot SELECT u.Id, u.ColName, u.ColValue FROM MyTable t UNPIVOT (ColValue FOR ColName IN (A0,A1,A2,A3,A4,A5,A6) u ; INSERT #ValueFrequency SELECT u.Id, u.ColumnValue, COUNT(*) FROM #unpivot u WHERE u.ColumnValue != ' ' -- do not count blanks GROUP BY u.Id, u.ColumnValue ORDER BY Id, Frequency DESC, ColumnValue ; WITH AllOneValue AS ( SELECT vf.Id FROM #ValueFrequency vf GROUP BY vf.Id HAVING COUNT(*) = 1 ) SELECT vf.Id, vf.ColumnValue, vf.Frequency , DSum = mt.Pd0 + mt.Pd1 + mt.Pd2 + mt.Pd3 + mt.Pd4 + mt.Pd5 + mt.Pd6 FROM MyTable mt INNER JOIN #ValueFrequency vf ON vf.Id = mt.Id INNER JOIN AllOneValue aov ON aov.Id = vf.Id ;
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Tom, this partially answer my question, doing the unpivot then I will loose the Ps fields that I need. The point is ....I need to count how many same values are in the As fields and compares the total to Ps. I need to know if the count of same values of A0 thru A6 is equal to the total sum of P0 thru P6 and also know what is the common value in the As columns Let's say in first row I have 6 "A" values in columns A0 thru A6 and 6 total points in P0 thru P6 and the value is "A". this is a good record. second row I have no values in A0 thru A6 and 6 total points in P0 thru P6. No good record. fifth row I have 2 "C" and 2 "A" in A0 thru A6 fields (so the value is not common to all the As fields) and 6 total points in Ps fields. No good record. I hope I was a little more clear (all As fields have to have same code, and if this happen, then the total count of the As has to match the total points (sum of Ps fields) to be good record)
0 Likes 0 ·
by the way...i need to know what is the common good value in the As fields to keep
0 Likes 0 ·
i get error here WITH AllOneValue AS ( SELECT vf.Id FROM #ValueFrequency vf HAVING COUNT(*) = 1 )
0 Likes 0 ·
Sorry. I corrected the error. If I had a dollar for every time I forgot the "GROUP BY", I'd be a rich man. It needed this between the FROM and HAVING: GROUP BY vf.Id
0 Likes 0 ·
Thanks a lot Tom for show me the path to follow,I changed a bit, but it worked very well.....thanks again!!!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.