Please help me to query out - 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

Please help me to query out

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2008-11-02 : 09:15:35

Hi there,
i have a history table that keeps a record for users actions ex:

History table having two columns

Docno History
------- -----------
1542 Send Notification To:Jamal
1542 Send Notification To: walid
1542 Document Read: Jamal
1542 Document Edited: Walid

i want to make a summary report which look like this

Docno User Action
------- ---- ------
1542 Jamal Sent To, Read
1542 Walid Sent To, Edited

what is the best way to do so please guide me
Thankss

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 10:31:22

[code]SELECT t.Docno,LEFT(hl.HistList,LEN(hl.HistList)-1) AS Action
FROM (SELECT DISTINCT Docno FROM Table)t
CROSS APPLY (SELECT REPLACE(REPLACE(History,'Document',''),'Notification','') + ','
FROM Table
WHERE Docno=t.Docno
FOR XML PATH(''))hl(HistList)[/code]

ann06
Posting Yak Master

171 Posts

Posted - 2008-11-03 : 00:50:44

I have sql server 2000 so the above will not work!!
notice that i originally had two columns the desired result should be three columns

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-11-03 : 02:04:43

I dont think you have enough information to do what you want.
How do you know the order of events?
What if there are multiple reads, sends or edits? How do you relate the events?
How do you show something received, edited, edited, sent, edited etc.

Oh, and you are better posting to SQL 2000 forum because the suggestions in the SQL 2005 forum will use lots of stuff to do this that you won't be able to use.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 02:10:40

try the udf method in this link

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

ann06
Posting Yak Master

171 Posts

Posted - 2008-11-03 : 03:10:40

ok let us forget about the concatenation let us assume i want the desired result be like this
for the events if they only exist once the value be yes, so it must search Docno and user if found the related actions put "yes" in the column if no puts "No""

Docno User Sent read Edited
----- ---- ---- ---- ------
1542 Jamal yes yes No
1542 Walid yes No yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 03:21:10

quote:
Originally posted by ann06

ok let us forget about the concatenation let us assume i want the desired result be like this
for the events if they only exist once the value be yes, so it must search Docno and user if found the related actions put "yes" in the column if no puts "No""

Docno User Sent read Edited
----- ---- ---- ---- ------
1542 Jamal yes yes No
1542 Walid yes No yes

SELECT Docno,
RIGHT(History,LEN(History)-CHARINDEX(':',History) AS User,
MAX(CASE WHEN History LIKE '%Send%' THEN 1 ELSE 0 END) AS Sent,
MAX(CASE WHEN History LIKE '%read%' THEN 1 ELSE 0 END) AS Read,
MAX(CASE WHEN History LIKE '%Edited%' THEN 1 ELSE 0 END) AS  Edited
FROM Table
GROUP BY  Docno,
RIGHT(History,LEN(History)-CHARINDEX(':',History)

ann06
Posting Yak Master

171 Posts

Posted - 2008-11-03 : 08:38:17

Thanks that guided me the way
I appreciate ur help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 09:10:08

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources