Combining Fields to string

I have a function that takes a field with values separated by commas within the field and splits them to multiple rows.

Example:
Field - Interior
Value - abc,def,efg,ghi

Output:
ID Item
1 abc
2 def
3 efg
etc

This is working great thanks to help that I received on here.

Now I am combining multiple fields to a string.
Example:
Field1: abc, def
Field2: ghi, jkl

using

SELECT (Field1 + ',' + Field2) From ....

This is working great unless there is a field that has a NULL value. Then I get a NULL result.

Is there an easy way to only put the fields with value into my string and leave out the NULL fields? Some have one NULL field, some have multiple. I just need to get the string to work and get only the fields that have values.

Any suggestions are always appreciated.It has been resolved on another post.

THANKS!!|||

Quote:

Originally Posted by rpeacock

It has been resolved on another post.

THANKS!!

I am having the same problem - can you tell me what other post solved the issue?

Thanks in advance

RIP

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

COLUMNS_UPDATED()

SQL Server 2000
BOL says:
The COLUMNS_UPDATED function returns the bits in order from left to right,
with the least significant bit being the leftmost. The leftmost bit
represents the first column in the table; the next bit to the right
represents the second column, and so on.
But in the example, bitmask to check the colums 2,3,4 calculated as 14,
in which rightmost bit is the first column in the table. is there something
wrong here?Yeah. what you are asking makes sense.
Looks like the bit stream is looked at as a string than a binary number, if
thats what you concern is.
Say for a 8 column table the first 4 are updated, then the columns_updated()
will read as
1111
and if 2 and 3 are updated its going to be
011
All it means is that the 0 has a significance to give out the position of
the column being updated or not and we cannot say 011 and 11 are equal in
this context.
and coming to your question,
14 is read is 0111 rather than 1110.
Its using a reverse binary system.. I believe.. But a good point you pointed
out nevertheless.
Lets wait for the other's comments though :)|||prefect a crit :
> SQL Server 2000
> BOL says:
> The COLUMNS_UPDATED function returns the bits in order from left to right,
> with the least significant bit being the leftmost. The leftmost bit
> represents the first column in the table; the next bit to the right
> represents the second column, and so on.
NOT AT ALL !
The bit calculate is based on the ordinal position deliver by
INFORMATION_SCHEMA.COLUMNS
Dmo :
CREATE TABLE T_TEST_BITCOLS_TBC
(COL1 INT,
COL2 INT,
COL3 INT,
COL4 INT,
COL5 INT)
GO
INSERT INTO T_TEST_BITCOLS_TBC VALUES (1, 2, 3, 4, 5)
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL2
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL3
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL5
GO
ALTER TABLE T_TEST_BITCOLS_TBC
ADD COL2 INT
GO
ALTER TABLE T_TEST_BITCOLS_TBC
ADD COL6 INT
GO
INSERT INTO T_TEST_BITCOLS_TBC VALUES (10, 20, 30, 40)
GO
CREATE TABLE T_TRIGGER_COLS_UPDATED_TCU
(TABLE_NAME SYSNAME,
BIT_COLS INT)
GO
CREATE TRIGGER E_U_TCU
ON T_TEST_BITCOLS_TBC
FOR UPDATE
AS
INSERT INTO T_TRIGGER_COLS_UPDATED_TCU
SELECT 'T_TEST_BITCOLS_TBC', COLUMNS_UPDATED()
GO
UPDATE T_TEST_BITCOLS_TBC
SET COL2 = 0
GO
SELECT *
FROM T_TRIGGER_COLS_UPDATED_TCU
TABLE_NAME BIT_COLS
-- --
T_TEST_BITCOLS_TBC 32
SELECT COLUMN_NAME, ORDINAL_POSITION,
POWER(2, ORDINAL_POSITION - 1) AS BIT_COL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'T_TEST_BITCOLS_TBC'
COLUMN_NAME ORDINAL_POSITION BIT_COL
-- -- --
COL1 1 1
COL4 4 8
COL2 6 32 <====
COL6 7 64

> But in the example, bitmask to check the colums 2,3,4 calculated as 14,
> in which rightmost bit is the first column in the table. is there somethi
ng
> wrong here?
YES !
>
A +
--
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||thanks , i guess rightmost bit of every byte corresponds to first one of
the every 8 column regarding the ordinal of column.
surely , bit order of columns is read from system tables.
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:OeCvlFFaGHA.1228@.TK2MSFTNGP02.phx.gbl...
> prefect a crit :
> NOT AT ALL !
>
> The bit calculate is based on the ordinal position deliver by
> INFORMATION_SCHEMA.COLUMNS
> Dmo :
>
> CREATE TABLE T_TEST_BITCOLS_TBC
> (COL1 INT,
> COL2 INT,
> COL3 INT,
> COL4 INT,
> COL5 INT)
> GO
> INSERT INTO T_TEST_BITCOLS_TBC VALUES (1, 2, 3, 4, 5)
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL2
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL3
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL5
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> ADD COL2 INT
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> ADD COL6 INT
> GO
> INSERT INTO T_TEST_BITCOLS_TBC VALUES (10, 20, 30, 40)
> GO
> CREATE TABLE T_TRIGGER_COLS_UPDATED_TCU
> (TABLE_NAME SYSNAME,
> BIT_COLS INT)
> GO
>
> CREATE TRIGGER E_U_TCU
> ON T_TEST_BITCOLS_TBC
> FOR UPDATE
> AS
> INSERT INTO T_TRIGGER_COLS_UPDATED_TCU
> SELECT 'T_TEST_BITCOLS_TBC', COLUMNS_UPDATED()
> GO
> UPDATE T_TEST_BITCOLS_TBC
> SET COL2 = 0
> GO
> SELECT *
> FROM T_TRIGGER_COLS_UPDATED_TCU
> TABLE_NAME BIT_COLS
> -- --
> T_TEST_BITCOLS_TBC 32
>
> SELECT COLUMN_NAME, ORDINAL_POSITION,
> POWER(2, ORDINAL_POSITION - 1) AS BIT_COL
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'T_TEST_BITCOLS_TBC'
> COLUMN_NAME ORDINAL_POSITION BIT_COL
> -- -- --
> COL1 1 1
> COL4 4 8
> COL2 6 32 <====
> COL6 7 64
>
>
>
> YES !
>
> A +
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

ColumnName Parameter for Table Valued Function

Hi All,

Is it possible to do the following:
1. I have a udf_xxx which returns Col1,Col2 and Col3
2. I need to join this udf_xxx to get Col1 and Col2 where Col3 matches with table tbl1.

SELECT udf.Col1, udf.Col2, t.Col3
FROM dbo.tbl1 t
JOIN dbo.udf_xxx (t.Col3) udf ON
t.Col3 = udf.Col3

Thanks in advance.In SQL Server 2005, you could use CROSS (OUTER) APPLY:
SELECT udf.Col1, udf.Col2, t.Col3
FROM dbo.tbl1 t
CROSS APPLY dbo.udf_xxx (t.Col3) udf|||I just happened to find out that too. Thanks Konstantin Kosinsky!

column update function

This is probably a common problem with a standard design pattern, but
I'm having trouble finding the solution.

I have a table with a lot of columns, for this example I'll just use
three but in reality its more like 20.

Create Table myTable (int col_one primary key, int col_two,
varchar(20) col_three) etc...

I want to write a sproc that allows updating of this column. Say I
have a sproc

create sproc myUpdate int @.col_one, int @.col_two, varchar(20)
col_three

as

update myTable col_two = @.col_two, col_three = @.col_three
where col_one = @.col_one

then if I only want to update col_two I have to pass in the current
value of col_three so that it remains the same, which seems pretty
inefficient. so I could change it to:

as

update myTable col_two = coalasce(@.col_two, col_two)
, col_three = coalasce(@.col_three, col_three)
where col_one = @.col_one

and then if I wanted to leave col_three the way it is then I could
just do

exec myUpdate 1, 2, NULL

the only problem here is that what if the value of col_three is
currently 3, and I want to set it to NULL? Under the current method,
setting someting to NULL is impossible

finally, I'd like to use parameter naming in my exec calls. that way
I can just say someting like

exec myUpdate 1, col_three=3

this would update col_three to 3 and leave the rest of the fields
untouched. you can see how handy this would be if you just want to
change a few of the fields in a table with a large number of columns.

I'm sure this has been done before, can somebody point me in the right
direction?

Thanks,

Benben (santoshamb@.yahoo.com) writes:
> update myTable col_two = coalasce(@.col_two, col_two)
> , col_three = coalasce(@.col_three, col_three)
> where col_one = @.col_one
> and then if I wanted to leave col_three the way it is then I could
> just do
> exec myUpdate 1, 2, NULL
> the only problem here is that what if the value of col_three is
> currently 3, and I want to set it to NULL? Under the current method,
> setting someting to NULL is impossible
> finally, I'd like to use parameter naming in my exec calls. that way
> I can just say someting like
> exec myUpdate 1, col_three=3
> this would update col_three to 3 and leave the rest of the fields
> untouched. you can see how handy this would be if you just want to
> change a few of the fields in a table with a large number of columns.

T-SQL is not a language that lends itself to this sort of thing. There
is no way to tell whether a parameter was passed explicitly or not. You
can of course test for NULL, but it may have been an explicit NULL.

One alternative would be to have extra marker variables to tell whether
a parameter applies or not. It quickly gets bulky. It can be reduced to
a single parameter which is a bitmask, but that is cryptic and error-prone.

What we do in our update procedures is to pass all column values. But
then we typically have read all to the GUI and now we are sending them
back. If some operation updates only affects a few columns, that is
typically an individual UPDATE statement in a different procedure.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.sqlmonster.com
Robert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/de...es_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.droptable.comRobert Richards via droptable.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/d...>
_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
--
Message posted via http://www.sqlmonster.comRobert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com

Column Ranking on SS 2000 and/or Reporting Services report

I see a Rank function on SQL Server 2005 and this is exactly what I want. I am writing a Reporting Services report on Sql Server 2000 and the Rank function is not available on either software. The following is my data and what I want...

Name Value Rank

A 5 2

B 5 2

C 6 1

D 4 4

E 2 5

F 1 6

Any ideas on how to code a rank function on SS 2000 or Reporting Services?This article describes using a table-valued function to perform ranking. http://www.devx.com/getHelpOn/10MinuteSolution/16499/1954?pf=true|||

The SQL function in the link may not provide you the result you are looking for, as in SQL Server 2005. For example, the function may return records with ranks like 1,2,3,3,4,5 but what you need is 1,2,3,3,5,6 as per sql 2005. To do that, write an update statement at the end of the function before returning.

UPDATE t1

SET Rank = Rank +

(SELECT SUM(COUNT(Rank)-1) FROM table1 t2 WHERE t2.Rank < t1.Rank

GROUP BY Rank HAVING COUNT(Rank) > 1)

FROM table1 t1

GROUP BY Rank

HAVING COUNT(*) = 1

ORDER BY Rank'

Shyam

|||Thanks. I noticed the result set from the link. Thanks for the code.|||

Can you please mark it as answer?

Shyam

Column Number function in a matrix?

I have a matrix and would like to be able to add to the column titles
the column number. I have been looking for a function maybe similar
to the rownumber() function, but cannot locate anything for column
number. Is this a possibility?
Thanks!On Jun 26, 6:42 pm, Just Another Reporter <Crystal.War...@.gmail.com>
wrote:
> I have a matrix and would like to be able to add to the column titles
> the column number. I have been looking for a function maybe similar
> to the rownumber() function, but cannot locate anything for column
> number. Is this a possibility?
> Thanks!
The best way to add this functionality is to add an extra column to
your dataset (query/stored procedure results) giving the column rank
(so to speak). If there are a manageable number of column values to
pivot, you can use case statements in the query/stored procedure to
assign the column rank value. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

column names of the table

Hi guys,

Is there any function that can the column names of the table? I know about the sp_help but I want I'm going to call this from my .net application?

Thanks

How about using the INFORMATION_SCHEMA.COLUMNS view? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:2ff402f1-91fb-4795-aee4-bc05d3efcffa@.discussions.microsoft.com...
> Hi guys, >
> Is there any function that can the column names of the table? I know
> about the sp_help but I want I'm going to call this from my .net
> application? > > >
> Thanks >
>|||try this
select column_name from information_schema.columns where table_name ='agents'|||Thank guys!!!

Column name in functions

Hi,

Can we use a parameter that is a column name in a function ?

Here's my function :

CREATE FUNCTION dbo.fn_counting (@.colnumber varchar(2),@.number
varchar(1))
RETURNS int AS

BEGIN
DECLARE @.column varchar(2)
DECLARE @.ColTotal int

SET @.column = 'R' +@.colnumber
(This next line WORKS !!!)
SELECT @.ColTotal = COUNT(*) FROM dbo.Tbl_Answers WHERE R3 = @.number
(This next one DOESN'T WORK - because of the ' it is treated as a
string)
SELECT @.ColTotal = 'COUNT(*) FROM dbo.Tbl_Answers WHERE ' +@.column +
'=' +@.number

RETURN @.ColTotal
END

Thank youNo. But with good design you should never need to. Why wouldn't you know the
column name at design time?

--
David Portas
SQL Server MVP
--|||Because my data table is filled with 40 answers (columns) from a survey
(4,3,2,1) for different group. Then the user will tell me which group,
year, etc he needs the data for and I need to count the number of
4,3,2,1 for that groups for every answer (column). Not really clear !!!

But obviously you are right I will rethink my approach

Thank you for the answer|||For example, try this:

CREATE TABLE Survey (group_no INTEGER NOT NULL REFERENCES Groups (group_no),
year_no INTEGER NOT NULL, answer_no INTEGER NOT NULL CHECK (answer_no
BETWEEN 1 AND 40), response INTEGER NOT NULL CHECK (response BETWEEN 1 AND
4), PRIMARY KEY (group_no, year_no, answer_no))

SELECT response, COUNT(*)
FROM Survey
WHERE group_no = @.group_no
AND year_no = @.year_no
GROUP BY response

--
David Portas
SQL Server MVP
--|||Patrik (patrik.maheux@.umontreal.ca) writes:
> Because my data table is filled with 40 answers (columns) from a survey
> (4,3,2,1) for different group. Then the user will tell me which group,
> year, etc he needs the data for and I need to count the number of
> 4,3,2,1 for that groups for every answer (column). Not really clear !!!
> But obviously you are right I will rethink my approach

You should most certainly make the columns into rows instead. The way
databases work, it's a lot easier to handle repeating groups if they
are rows instead of columns.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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

Thank you again for the help

Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > Because my data table is filled with 40 answers (columns) from a survey
> > (4,3,2,1) for different group. Then the user will tell me which group,
> > year, etc he needs the data for and I need to count the number of
> > 4,3,2,1 for that groups for every answer (column). Not really clear !!!
> > But obviously you are right I will rethink my approach
> You should most certainly make the columns into rows instead. The way
> databases work, it's a lot easier to handle repeating groups if they
> are rows instead of columns.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||The format the data is supplied in should not dictate the database design.
Design the database correctly and then develop a process to load the data
into that database from its external source.

--
David Portas
SQL Server MVP
--|||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-...ist-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/techin.../2000/books.asp

column collation

Hi,
I am using C++ to develop an application to support SQL Server 2005. I have
a problem whant I use SQLColAttribute function. It only returns "Collation
Name", instead of the real column's collation name. Does any body has any
idea?
Here it is my code:
retcode = SQLColAttribute(handleSTMT,i +1,
SQL_CA_SS_COLUMN_COLLATION ,
columnCollation,
256, &nameLength, &valueInInt);
Thanks for you help.
--Lijie
I believe is not supported in SQLColAttribute

column collation

Hi,
I am using C++ to develop an application to support SQL Server 2005. I have
a problem whant I use SQLColAttribute function. It only returns "Collation
Name", instead of the real column's collation name. Does any body has any
idea?
Here it is my code:
retcode = SQLColAttribute(handleSTMT,i +1,
SQL_CA_SS_COLUMN_COLLATION ,
columnCollation,
256, &nameLength, &valueInInt);
Thanks for you help.
--LijieI believe is not supported in SQLColAttribute