Posted by Erland Sommarskog on 10/01/59 11:17

Patrik (patrik.maheux@umontreal.ca) writes:
> I think cannot make my columns into rows because the data comes like
> that from an optical reader in a text format that I import.Let me be
> clearer :
>
> My main table is autokey-year-personcode-Answer1 thru 40 (43 columns).
> I can have 125 respondants(rows) for one code thus the autoid
> DATA looks like: 2000-101-4-3-3-4-2-1-3-4-2-3-2...thousands of lines
> like these
>
> Then I need to count the number of 4-3-2 and 1 for every personcode.
>
> I will try the proposed solution and let the group know if it works

As David said, don't let the input format dictate your data model. That
format will give you a headache somewhere on the line, and I'm telling
you the earlier you handle it in the process, the less headache you will
get.

For this case, I would unpack the string with a list-to-table function,
see http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-integers
for such a function. For your case you would have handle listpos 1, 2
and 3 individually, and then the answers would be everything above 4.
You could use the function as is, but you could also adapt it so it
directly unpacks into the format you need.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]