replace data and concat field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

replace data and concat field

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-04-01 : 03:58:30

Hi all,

I have table as :
ID--------IP---------QuestionID----Answer---OtherAnswer---IsSpecial
1---203.155.168.11--------1----------A---------0---------------0
1---203.155.168.11--------1----------A1--------0---------------0
3---203.118.118.13--------1----------B---------0---------------0
4---203.137.168.19--------1----------Y--------"123456"---------1
5---166.122.168.35--------1----------F---------0---------------0
6---223.115.168.77--------2----------Y1-------"987654"---------1

Expected output:
* if isspecial=1 i will replace otheranswer into answer:

ID--------IP---------QuestionID----Answer-----OtherAnswer---IsSpecial
1---203.155.168.11--------1----------A---------0---------------0
1---203.155.168.11--------1----------A1--------0---------------0
3---203.118.118.13--------1----------B---------0---------------0
4---203.137.168.19--------1----------"123456"--"123456"---------1
5---166.122.168.35--------1----------F---------0---------------0
6---223.115.168.77--------2----------"987654"--"987654"---------1

* I want to group by ip ,add to "Final answer"

ID--------IP---------QuestionID----Answer----IsSpecial-- FinalAnswer
1---203.155.168.11--------1----------A---------0--------------A+A1
3---203.118.118.13--------1----------B---------0---------------B
4---203.137.168.19--------1---------"123456"---1-------------"123456"
5---166.122.168.35--------1----------F---------0--------------F
6---223.115.168.77--------2---------"987654"---1--------------"987654"

Any one help me ??
Thank you very much !

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-01 : 05:20:22

Here is the solution


CREATE TABLE tbl (ID int, IP varchar(20), QuestionID int, Answer varchar(20), OtherAnswer varchar(100) ,IsSpecial bit)

INSERT INTO tbl
SELECT 1,'203.155.168.11',1,'A','0',0
UNION ALL
SELECT 1,'203.155.168.11',1,'A1','0',0
UNION ALL
SELECT 3,'203.118.118.13',1,'B','0',0
UNION ALL
SELECT 4,'203.137.168.19',1,'Y','"123456"',1
UNION ALL
SELECT 5,'166.122.168.35',1,'F','0',0
UNION ALL
SELECT 6,'223.115.168.77',2,'Y1','"987654"',1


with cte as
(
select id, ip, questionid, CASE WHEN IsSpecial = 1 THEN OtherAnswer ELSE Answer END AS Answer, OtherAnswer, IsSpecial,
ROW_NUMBER() OVER(Partition by ip order by id) seq
FROM tbl
)
select id, ip, questionid, Answer,
STUFF((SELECT '+'+Answer FROM CTE WHERE id=c.id FOR XML PATH('')),1,1,'') as FinalAnswer, isspecial
FROM cte c
WHERE seq = 1 
order by id

What is logic for final answer to be A+B
as if you want group by ip
according to me output should be like below

1 203.155.168.11 1 A A+A1 0
3 203.118.118.13 1 B B 0
4 203.137.168.19 1 "123456" "123456" 1
5 166.122.168.35 1 F F 0
6 223.115.168.77 2 "987654" "987654" 1

Vaibhav T

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-04-01 : 05:35:40

Good sql statement ! Can i replace your tbl with "Select * from my table" ?? How can i do ?
Because my data join many table ! i get a example !

with cte as
(
select id, ip, questionid, CASE WHEN IsSpecial = 1 THEN OtherAnswer ELSE Answer END AS Answer, OtherAnswer, IsSpecial,
ROW_NUMBER() OVER(Partition by ip order by id) seq
FROM "Select * from ...inner join...."
)
select id, ip, questionid, Answer,
STUFF((SELECT '+'+Answer FROM CTE WHERE id=c.id FOR XML PATH('')),1,1,'') as FinalAnswer, isspecial
FROM cte c
WHERE seq = 1
order by id

Is that ok ? All Field in my select are same all field in your tbl !
Thank for your attension !

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-04-01 : 05:47:27

I did it !! I really want to say thank you very much !! ^^

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-01 : 05:58:50

You are welcome

I am glad that i could help you...

Vaibhav T

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-04-01 : 23:03:15

Hi, i have discover that it occurs small problem,if same IP but different QuestionID ,your statement get only one record.
Example :
ID--------IP---------QuestionID----Answer---OtherAnswer---IsSpecial
1---203.155.168.11--------1----------A---------0---------------0
1---203.155.168.11--------1----------A1--------0---------------0
3---203.118.118.13--------1----------B---------0---------------0
4---203.137.168.19--------1----------Y--------"123456"---------1
5---203.155.168.11--------2----------OP--------0---------------0
6---203.155.168.11--------2----------HD-------"987654"---------1

My problem group by Question ID:
Expected output:

ID--------IP---------QuestionID--------IsSpecial-- FinalAnswer
1---203.155.168.11--------1-----------------0---------------A+A1
5---203.155.168.11--------2-----------------1---------------OP+"987654"

Can you correct help me ??
THank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-01 : 23:09:29

try change to


Partition by ip, QuestionID

KH
[spoiler]Time is always against us[/spoiler]

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-04-01 : 23:30:45

quote:
Originally posted by khtan

try change to

Partition by ip, QuestionID

KH
[spoiler]Time is always against us[/spoiler]

I have just try your suggestion ! But still wrong with "Final Answer" field !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 02:56:37

[code]
DECLARE @IPTable table
(
ID int,
IP varchar(20),
QuestionID int,
Answer varchar(5),
OtherAnswer varchar(6),
IsSpecial bit
)
INSERT @IPTable
SELECT 1,'203.155.168.11',1,'A','0',0 union all
SELECT 1,'203.155.168.11',1,'A1','0',0 union all
SELECT 3,'203.118.118.13',1,'B','0',0 union all
SELECT 4,'203.137.168.19',1,'Y','123456',1 union all
SELECT 5,'203.155.168.11',2,'OP','0',0 union all
SELECT 6,'203.155.168.11',2,'HD','987654',1

;With CTE (RowNo,ID,IP,QuestionID,Answer,IsSpecial) AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY IP,QuestionID ORDER BY Answer),
ID,IP,QuestionID,
CASE WHEN IsSpecial=1 THEN OtherAnswer ELSE Answer END,IsSpecial-- FinalAnswer
FROM @IPTable
)
SELECT ID,IP,QuestionID,Answer,IsSpecial,
STUFF((SELECT '+' + Answer FROM CTE WHERE IP=c.IP AND QuestionID=c.QuestionID FOR XML PATH('')),1,1,'') AS FinalAnswer
FROM CTE c
WHERE c.RowNo=1
ORDER BY ID

output
---------------------------------
ID IP QuestionID Answer IsSpecial FinalAnswer
1 203.155.168.11 1 A 0 A+A1
3 203.118.118.13 1 B 0 B
4 203.137.168.19 1 123456 1 123456
6 203.155.168.11 2 987654 1 OP+987654

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-04-02 : 04:01:05

Thanks visakh16 ^^ ! Problem has been solved !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 04:04:40

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources