convert row to column?

Hi,
I have a row with 53 columns. All but the first three are of the same datatype.
I want to count the number of columns, out of the last 48 in that row, that have the same value. Is there a nifty way to do this with SQL or T-SQL?

Sample row:

a,b,c,1,1,1,2,1,2,2,2,3,3,3,4,4, etc

The values are not static. I want toknow how many 1's how many 2's etc.

Of course within the app I could for example put the row in a .net datarow and loop through the columns.

Thanks,
B.

my first thought was to insert all the column values into a temp table then do a select distinct on it. not very efficient though|||

The real issue is how you want the data returned. Normally, you'd want something like a data row for every a,b,c, followed, by the number, followed by the count of that number. For your example, you would have 4 rows (or more):
a,b,c,1,3
a,b,c,2,4
a,b,c,3,3
a,b,c,4,2

The old fashioned way would be to create a monster statement like:

SELECT col1,col2,col3,col4
UNION ALL
SELECT col1,col2,col3,col5
UNION ALL
SELECT col1,col2,col3,col6
...

Once you have that done (all 48 UNIONED), then you can play with it:

SELECT col1,col2,col3,col4,COUNT(*) as [Count]
FROM ( {Insert the SELECT/UNION/SELECT/UNION Here} ) t1
GROUP BY col1,col2,col3,col4

You can possibly avoid some of this by using the new UNPIVOT clause as well, but I'm not familiar enough to rattle it's syntax off the top of my head.

|||

Here is unpivot solution:

SELECT

col1, col2, col3, cols,count(cols)as myColCountFROM

(

select

col1, col2, col3, myCol, colsFROM

(

SELECT col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24,

col25

, col26, col27, col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40, col41, col42, col43, col44, col45, col46, col47, col48, col49, col50, col51, col52, col53

FROM

onerow2) p

UNPIVOT

(colsFOR myColIN

(

col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24,

col25

, col26, col27, col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40, col41, col42, col43, col44, col45, col46, col47, col48, col49, col50, col51, col52, col53

)

)

AS unPvt)as myNewTable

GROUP

BY col1, col2, col3, cols

Limno

|||

Thanks for the reply

The values after the first 3 (a,b,c) are user preferences. I need something like :

Adam, 4, 13
Adam, 3, 7
Adam, 2, 16
Adam, 1, 12

to know that Adam has 13 4's 7 3's etc.

I'll try the monster union statement and the UNPIVOT

Currently I'm inserting in a temptable

|||

Thanks

That's what I'm using now but trying to improve efficiency