Concatenate Rows

Hi

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

go

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

From what I can tell, all the easy solutions for this are in SQL2005. SQL2000 solutions are much messier. Try searching this forum for words like aggregate and concatenate. There are a few that might help, like this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125302&SiteID=1

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, count(*) as seq
from your_table as t1
join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment
) as t3
group by t3.id;

Thank you all for your help.

Concatenate Rows

Hi

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

go

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

From what I can tell, all the easy solutions for this are in SQL2005. SQL2000 solutions are much messier. Try searching this forum for words like aggregate and concatenate. There are a few that might help, like this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125302&SiteID=1

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, count(*) as seq
from your_table as t1
join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment
) as t3
group by t3.id;

Thank you all for your help.

complicated Join - duplicate row problem

Hi
I had a similar problem awhile back and it was solved here. Now it has
gotten more complex. I have removed uneeded stuff here to keep this
simple.:
I am sure I know why I am getting the results I am, but don't know
how (or if) there is a way around it. If someone can help, I will be
mighty excited and impressed !
Here goes:
Given a table definition:
CREATE TABLE table1
(col1 varchar(20),
col2 varchar(20),
col3 integer,
CONSTRAINT PK_table1 PRIMARY KEY (col1,col2))
and then (assuming table2 exists)
ALTER TABLE table1
ADD CONSTRAINT FK_table1_table2
FOREIGN KEY (col1)
REFERENCES table2 (col1)
REQUIREMENT:
I would like to select some schema information about this table. Among
other things, I want the column name, data type, and Primary Key and
Foreign Key information. For the Primary Key and Foreign Key, all I
need to know is if one or both of these attributes applies to a
column.
Therefore, I would like my result records to look like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK | FK
col2 | varchar | PK |
col3 | integer | |
My problem is getting the PK and FK on the same row. My results are
currently like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK |
col1 | varchar | | FK
col2 | varchar | PK |
col3 | integer | |
HERE IS THE QUERY:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END,
FK_Col = case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
So, Col1 is duplicated because there are two (2) entries (PK & FK) in
key_column_usage for that column.
Is there some way to combine these two result rows together taking the
PK info from 1 and the FK from the other?
Thanks
JeffHi, Jeff
Did you read my response to your previous post ?
http://groups.google.com/group/micr...br />
a2bf3ce9
It's exactly what you are requesting now...
Razvan|||Sorry, I had missed your earlier reply because it didn't show up in my
news reader under the question. I see it now. My mistake.
This looks good and makes sense.
Thank you very very much !!
Jeff
On 12 Jan 2006 23:28:37 -0800, "Razvan Socol" <rsocol@.gmail.com>
wrote:

>Hi, Jeff
>Did you read my response to your previous post ?
>http://groups.google.com/group/micr...r />
9a2bf3ce9
>It's exactly what you are requesting now...
>Razvan|||On Fri, 13 Jan 2006 06:38:33 GMT, Jeff User wrote:
(snip)
>Therefore, I would like my result records to look like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK | FK
> col2 | varchar | PK |
> col3 | integer | |
>My problem is getting the PK and FK on the same row. My results are
>currently like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK |
> col1 | varchar | | FK
> col2 | varchar | PK |
> col3 | integer | |
(snip)
Hi Jeff,
Just a few simple changes to the query should suffice:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = MAX(case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END),
FK_Col = MAX(case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END)
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
GROUP BY cols.COLUMN_NAME, cols.DATA_TYPE, cols.ORDINAL_POSITION
Hugo Kornelis, SQL Server MVP

comparison between columns

How to compare the values between columns? I know there's a function "Greatest" in Oracle, do MS SQL have function similar to that?

And I want to create a view from a table, and one column 'll be selected from the 1 of the 4 related columns in table, say, If ColA is not null then the value is from ColA, if not , the value of the column in the view 'll be from ColB. How can i do this? Do I have to use store procedure?
( I know that's the result of poor normalization, but ... I'm not allowed to change th schema)sql/server apparently does not have anything as useful as the GREATEST function

unless someone else has a better idea, you'll have to use the CASE structure to build the equivalent --

select
(case
when a>=b and a>=c and a>=d then a
when b>=a and b>=c and b>=d then b
when c>=a and c>=b and c>=d then c
when d>=a and d>=b and d>=c then d
else a
end) as greatest

as for your other question, that's easy --

select coalesce(colA, colB) as ifAnullthenB

rudy
http://rudy.ca/|||What happens if you have Nulls?

Code:
---------------------------------------
create table #Tmp(a int, b int, c int, d int)

insert into #Tmp values(1, Null, Null, Null)
insert into #Tmp values(Null, 2, Null, Null)
insert into #Tmp values(Null, Null, 3, Null)
insert into #Tmp values(Null, Null, Null, 4)

insert into #Tmp values(5, 4, 3, 2)
insert into #Tmp values(3, 6, 4, 5)
insert into #Tmp values(4, 5, 7, 6)
insert into #Tmp values(5, 6, 7, 8)

select case
when a >= b and a >= c and a >= d then a
when b >= a and b >= c and b >= d then b
when c >= a and c >= b and c >= d then c
when d >= a and d >= b and d >= c then d
else a
end as greatest
from #Tmp

select case
when (b >= a or a is null) and (b >= c or c is null) and (b >= d or d is null) then b
when (c >= a or a is null) and (c >= b or b is null) and (c >= d or d is null) then c
when (d >= a or a is null) and (d >= b or b is null) and (d >= c or c is null) then d
else a
end as greatest
from #Tmp
---------------------------------------

Results:
---------------------------------------
greatest
----
1
NULL
NULL
NULL
5
6
7
8

greatest
----
1
2
3
4
5
6
7
8
---------------------------------------

Which is correct?

Comparing two strings inside a stored procedure

Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure

For example:
String1 = "Here is some data. lets type some more data"
String2 = "Here's some data. Lets type some data here"

I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.

So I would want string2 to look like this

string2 = "Here<font color = \"#00FF00\">'s</font> <strike>is</strike> some data. <font color = \"#00FF00\">L</font>ets type some <strike>more</strike> data <font color = \"#00FF00\">here</font>"

Is there an way to accomplish this inside a stored procedure?

First, you'll have to decide what algorithm determines what matches and what is different. For example, if you just compare the strings by the same position, you'll get the first part the same, the end of the first string as deleted and the end of the second string as inserted. I don't know how to implement the algorithm but I can think of some possibilities:

1. Regular expressions may have some support for this.

2. Track the user's changes character by character on the client.

3. Find a differencing algorithm / tool somewhere.

Once you've solved that, I suggest you pass the difference string to your procedure. You'll have much more flexibility and support in .NET than in SQL.

Sorry I couldn't help more. Good Luck.

|||Yes, you can. However, that is best left up to an application as it's a matter of presentation and application logic.