Is there a way to do this without cursors?

/*
All rows in #T1 for the same HId and with the same Priority 
should be combined into a single row in #T2. When creating a merged row, 
the Events column in #T2 should be a concatenation of all of the events 
of the distinct (HId, Priority) that were merged. 
*/
_____________________________
DDL:
_____________________________

CREATE TABLE #T1 
(
 HId int not null, 
 Priority tinyint not null, 
 [Event] varchar(30) Not Null,
 constraint [PKT1] primary key clustered 
(
 HId ASC, Priority ASC, [Event] ASC
)
)
GO
insert into #T1
select   1,5,'C'
union select 1,5,'R9'
union select 1,7,'F'
union select 1,7,'X'
union select 5,5,'C'
union select 5,5,'D'
union select 5,5,'X2'
union select 8,5,'R9'
union select 51,5,'A'
union select 2222,1,'Manual generation'
union select 2222,5,'C'
union select 2222,5,'P'
GO

CREATE TABLE #T2
(
 T2Id int identity(1,1) NOT NULL primary key,
 HId int NOT NULL,
 Priority [tinyint] NOT NULL,
 [Events] varchar(300) NOT NULL
 )
GO

___________________________
Sample data:
___________________________

SELECT * FROM #T1

HId Priority Event
HId,Priority,Event
1,5,C
1,5,R9
1,7,F
1,7,X
5,5,C
5,5,D
5,5,X2
8,5,R9
51,5,A
2222,1,Manual generation
2222,5,C
2222,5,P


SELECT * FROM #T2 

T2Id HId Priority Events
1,1,5,C + R9
2,1,7,F + X
3,5,5,C + D + X2
4,8,5,R9
5,51,5,A
6,2222,1,Manual generation
7,2222,5,C + P

Thanks in advance,
0
dm1606
12/13/2009 6:55:35 AM
sqlserver.programming 1873 articles. 0 followers. Follow

6 Replies
559 Views

Similar Articles

[PageSpeed] 34

If you are on SQL Server 2000 or earlier, have a look at
http://groups.google.nl/group/microsoft.public.sqlserver.programming/browse_thread/thread/d9453cef772977ae/2d85bf366dd9e73e#2d85bf366dd9e73e
(URL may wrap) This post describes 5 methods.

If you are on SQL Server 2005 or later, have a look at
http://groups.google.nl/group/microsoft.public.sqlserver.programming/msg/8762789293c43627?dmode=source
This post describes FOR XML PATH in combination with CROSS APPLY

-- 
Gert-Jan
SQL Server MVP

dm1606 wrote:
> 
> /*
> All rows in #T1 for the same HId and with the same Priority
> should be combined into a single row in #T2. When creating a merged row,
> the Events column in #T2 should be a concatenation of all of the events
> of the distinct (HId, Priority) that were merged.
> */
> _____________________________
> DDL:
> _____________________________
> 
> CREATE TABLE #T1
> (
>  HId int not null,
>  Priority tinyint not null,
>  [Event] varchar(30) Not Null,
>  constraint [PKT1] primary key clustered
> (
>  HId ASC, Priority ASC, [Event] ASC
> )
> )
> GO
> insert into #T1
> select   1,5,'C'
> union select 1,5,'R9'
> union select 1,7,'F'
> union select 1,7,'X'
> union select 5,5,'C'
> union select 5,5,'D'
> union select 5,5,'X2'
> union select 8,5,'R9'
> union select 51,5,'A'
> union select 2222,1,'Manual generation'
> union select 2222,5,'C'
> union select 2222,5,'P'
> GO
> 
> CREATE TABLE #T2
> (
>  T2Id int identity(1,1) NOT NULL primary key,
>  HId int NOT NULL,
>  Priority [tinyint] NOT NULL,
>  [Events] varchar(300) NOT NULL
>  )
> GO
> 
> ___________________________
> Sample data:
> ___________________________
> 
> SELECT * FROM #T1
> 
> HId Priority Event
> HId,Priority,Event
> 1,5,C
> 1,5,R9
> 1,7,F
> 1,7,X
> 5,5,C
> 5,5,D
> 5,5,X2
> 8,5,R9
> 51,5,A
> 2222,1,Manual generation
> 2222,5,C
> 2222,5,P
> 
> SELECT * FROM #T2
> 
> T2Id HId Priority Events
> 1,1,5,C + R9
> 2,1,7,F + X
> 3,5,5,C + D + X2
> 4,8,5,R9
> 5,51,5,A
> 6,2222,1,Manual generation
> 7,2222,5,C + P
> 
> Thanks in advance,
0
Gert
12/13/2009 12:17:34 PM
Here is one solution:

SELECT DISTINCT hid, [priority], STUFF(event_list, 1, 3, '') AS [events]
FROM #T1 AS A
CROSS APPLY(SELECT ' + ' + [event]
             FROM #T1 AS B
             WHERE B.hid = A.hid
               AND B.priority = A.priority
             FOR XML PATH('')) AS C(event_list);

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
12/13/2009 4:43:15 PM
Thank you, Gert-Jan.
Thank you, Plamen.
Your reply answers my question.
0
dm1606
12/13/2009 5:13:06 PM
Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure?  Normal forms are the foundation of RDBMS, after
all.

Why are you formatting data in the back end?  The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end.  This is a more basic programming principle than just
SQL and RDBMS.

Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.
All you will get here is kludges with XML, cursors or other
proprietary procedural code tricks.
0
CELKO
12/13/2009 6:30:43 PM
> Get a reporting tool so you can do a sort for your lists and lay them
> out on paper or a screen in a way that a human being can read them.
> All you will get here is kludges with XML, cursors or other
> proprietary procedural code tricks.

Why on earth would you buy (yes, spend money) on yet another product to 
learn when you can do this in the product you already have.

If you want to do pure standard SQL then fine - just get on with it, but to 
ignore the other 90% of the features in the product is an insult to your 
employers trust in you to get the full return on their investment, the money 
that would otherwise be used for their kids inheritance.

SQL Server is a data engine - stop trying to shoe horn it to being just a 
store and retrieve ISO SQL product.

--ROGGIE--


"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:3a11f667-4e51-4162-b0d7-ade53963e7a1@n35g2000yqm.googlegroups.com...
> Why do you wish to destroy First Normal Form (1NF) with a concatenated
> list structure?  Normal forms are the foundation of RDBMS, after
> all.
>
> Why are you formatting data in the back end?  The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end.  This is a more basic programming principle than just
> SQL and RDBMS.
>
> Get a reporting tool so you can do a sort for your lists and lay them
> out on paper or a screen in a way that a human being can read them.
> All you will get here is kludges with XML, cursors or other
> proprietary procedural code tricks. 

0
Tony
12/13/2009 7:07:14 PM
"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:3a11f667-4e51-4162-b0d7-ade53963e7a1@n35g2000yqm.googlegroups.com...
> Why do you wish to destroy First Normal Form (1NF) with a concatenated
> list structure?  Normal forms are the foundation of RDBMS, after
> all.
>
> Why are you formatting data in the back end?  The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end.  This is a more basic programming principle than just
> SQL and RDBMS.
>
> Get a reporting tool so you can do a sort for your lists and lay them
> out on paper or a screen in a way that a human being can read them.
> All you will get here is kludges with XML, cursors or other
> proprietary procedural code tricks.



Joe,
I know, I know... :-(
I couldn't agree with you more. Your comment and criticism are much 
welcomed. And it's good that you emphasize the importance of normalization. 
Someone has to do it...

On the other hand, this is not my choice and I don't want to... destroy 
anything. :-) Moreover, I hate to work that way, but I am paid to do that. 
AND I AM PAID TO DO IT EXACTLY THAT WAY!!! :-) There is a customer request, 
there is a project manager, there are specifications and I'm the one that 
has to write the code. This is the way db dev works in real-life 
companies...

P.S.
And Tony is right too. :-)))

0
dm1606
12/13/2009 8:13:17 PM
Reply:

Similar Artilces:

Easier way to grant mailbox rights to 100 mailboxes??
Consider the following scenario... Sally is a manager at a company. She wants to be able to monitor her staff's email usage. I want to be able to give her access without having to spend a bunch of time every time an employee is hired/moved/ fired/leaves. George is Sally's boss. George doesn't want Sally to have access to his mailbox other than what he delegates her access to. Originally, we just gave Sally full rights to the mailbox store, but as you probably know, that fails George's requirement. Is there an easy way to accomplish all this without a ton of administrative w...

CTreeCtrl icons without 256 colours
I am using a tree from CTreeCtrl. To create the imagelist I am using a bitmap with 256 colours. But the icons in my tree only appear with 16 colours. What is supposed to do to have icons with 256 colours in tree? This is my code: ------------------------------------------------------------- CTreeCtrl& lcl_tree = GetTreeCtrl(); CImageList icl_listaImagem; icl_listaImagem.Create( IDB_BITMAP_VECTOR_IMAGEM256, 17, 1, RGB(5,255,5) ); lcl_tree.SetImageList(&icl_listaImagem, TVSIL_NORMAL); // Set tree control styles long style = GetWindowLong(lcl_tree.m_hWnd,GWL_STYLE); style |= (TVS_HAS...

Restore Outlook 2000 without .pst?
I backed up a computer to a dvd but forgot to export outlook 2000 to a pst. Is there anyway to get the info back?jho In news:1123958637.749834.33580@g44g2000cwa.googlegroups.com, jho <jhogan0101@yahoo.com> typed: > I backed up a computer to a dvd but forgot to export outlook 2000 to a > pst. Is there anyway to get the info back?jho No. If you don't have the PST file, you don't have any data. Note - you don't need to export anything. You can just copy/back up the existing PST file(s) next time. jho <jhogan0101@yahoo.com> wrote: > I backed up a comput...

Cell Pop-Up Notes: Any way to extract them programatically?
I've got a sheet that lists various mutual funds and their returns in it's columns. However, somebody has put the inception date of each fund in a pop-up note attached to the fund column. i.e. the cell with fund's name has a little red triangle in it's upper right corner and when the cursor hovers over it, a yellow note pops up. I'm going at this sheet via a VBA Excel.Application object and would like to extract this data. My guess would be that each cell can have a collection of something-or-other that points to the pop-up notes. Anybody know for sure? There's s...

17 ways to Optimize VBA Code for FASTER Macros
Here is a summary of the article: 1. Analyze the Logic 2. Turn off ScreenUpdating 3. Turn off 'Automatic Calculations' 4. Disable Events 5. Hide Page breaks 6. Use 'WITH' statement 7. Use vbNullString instead of "" 8. Release memory of Object variables 9. Reduce the number of lines using colon(:) 10. Prefer constants 11. Avoid Unnecessary Copy and Paste 12. Clear the Clipboard after Paste 13. Avoid 'Macro Recorder'...

Is there a way to put 2 charts on the same display page?
I use a PIE chart because the numbers in my segments cvary greatly. I would like to put 2 pie charts side by side on a display page so I may show 2007 and 2008. Can this be done? Here's some instructions http://lilt.ilstu.edu/jpda/charts/chart%20tips/Chartstip%202.htm But when Jon Peltier sees your message, I'm sure he will tell you that the only thing worse than a Pie chart is two of 'em. On May 13, 2:54 pm, nedwards <nedwa...@discussions.microsoft.com> wrote: > I use a PIE chart because the numbers in my segments cvary greatly. I would > like to put 2 pie char...

Printing Publisher File on Computer without Publisher
I currently have Publisher 2002 but I don't have a printer. My friend has a printer I can use but he doesn't have Publisher installed on his computer. Is there a way to print publisher files on another computer that doesn't have publisher? Any help would be much appreciated. Thanks so much. Convert the file to PDF, Adobe offers five free conversions on their web site. https://createpdf.adobe.com/index.pl/4207686881.89851?BP=IE&v=AHP Click the "Try it for free!" button on the left bottom. -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/...

Is there a way
I am importing acsii data in the format detailed below and need to compare the addresses to find the ones that don't match. If they don't match I want to keep that record and delete the records that match. Is this something that can be done with a Macro? Any help or suggestions would be greatly appreciated... thanks david a b c d |-------------------------------------------------- 1 | 1223 main st | 1223 | main | st | |-------------------------------------------------- ...

Looking for Easiest Way to Create Report
We need to create a report from our accounting program which contains about 200 .db files. I have determined that the required information is contained in only 3 of those files. Out of those 3 files I need about 30 fields of data. The end result is that those 30 fields of data have to be placed into an existing excel spreadsheet in specific columns which has 97 total columns. For example one db file contains data which has to be placed into column c of the excel spreadsheet. another db file has data which has to be placed into column z of that same spreadsheet and so on Th...

change worksheet data without changing chart
I am attempting to create a scatter plot chart in Excel that will not get modified each time I sort my Excel worksheet. Does anybody know how to "lock" the chart or export it so that it is no longer linked to the worksheet? Thanks!!! Here's how to unlink a chart from its data: http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ shelly wrote: > I am attempting to create a scatter plot chart in Excel > that will not ge...

Need better way to provision AD accounts that are to have mailboxes
The exchange admins in my company have come up with a way for the sys admins to assign the user's mailbox store location. It's based off of last name. For example, if your last name starts with letter A, you go on Storage Group 1, Mailbox Store A, if your last name starts with letter B, you go on Storage Group 1, Mailbox Store B, if your last name starts with letter E, you go on Storage Group 2, Mailbox Store A, etc.... This is, I believe, and administrative nightmare. Is there a better way to provision AD accounts that are to have mailboxes? If it works for you and them, then al...

Determining Cursor Position
Hello again, have a quick little problem I am hoping someone can help me with. I have an email client I have written which contains 3 text fields (To:, Subject:, Body:) and I also have added a menu to my program. Now I can easily hit CTRL-V with the keyboard and PASTE, or else right-click the mouse and paste into a specified field, but I want to know how to use a MENU command to do this. Originally I tried simply using keybd_event(VK_CONTROL,1, 0, 0); keybd_event(22, 1, 0, 0); keybd_event(22, 1, KEYEVENTF_KEYUP, 0); // release V Key keybd_event(VK_CONTROL, 1, KEYEVENTF_KEYUP, 0); //...

Ways to view your tasks
Hi, Is it possible to view your tasks pane in your Inbox so that you don't have to keep switching between the two sceens of Mailbox & Inbox? Our company is running Outlook 2003 if it matters. Many thanks. -- Dave Mitch "Dave Mitch" <DaveMitch@discussions.microsoft.com> wrote in message news:B9093CC5-0C1F-4907-A308-DC58D1060571@microsoft.com... > Is it possible to view your tasks pane in your Inbox so that you don't have > to keep switching between the two sceens of Mailbox & Inbox? Not with Outlook 2003, but you can certainly have ...

What is the best way to Write this Formula
THank you to all that have helped and to who will help, i greatly appreciate it. Here is my question. I have four columns of data:b is OPEN, C is HIGH, D is LOW and D is CLOSE. I then wrote an =if(and)) statement with about six logical steps to come up with the words "BUY" and "SELL" in columns f and g respectfully. Then in column H I wrote a buy stop formula which is =if(f2="BUY",e2-6.00,""). Worked great got my new number now what I need to write is and this is the hard part IF at any time that the LOW(column d) from that period on until th...

Want more speed and a cool way of using CRM?
Try www.maxthon.com. This is not really a plug as it is me getting excited about a tool that really helps me use CRM better. The tabbed browsing once configured really help keep things organized. It is also significantly faster than base IE. Try it out. This is excellent, thanks Jake. Dan "Jake Horn" wrote: > Try www.maxthon.com. > > This is not really a plug as it is me getting excited about a tool that > really helps me use CRM better. The tabbed browsing once configured really > help keep things organized. It is also significantly faster than base I...

Cleaner way to query for negatives?
Access 2000, Win XP SP2 Hi all, I'm looking for a cleaner way to determine whether or not an entry in one table has a specific field checked yes or no in another table. Table 1: fldVendorID (Primary key, no dupes) fldVendorName Table 2: fldVendorID (dupes allowed) fldContact fldAllowed Table 2 can have many fldContacts per each fldVendorID from Table 1. fldAllowed is a yes/no field. I want to query for any VendorID which has NO fldAllowed=yes. I've done it this way: Query 1 counts all contacts from Table 2, grouped by fldVendorID Query 2 counts all fldAllowed="No"...

how to code "close without saving"
How can I code a form to close without saving. do not want to even save the autonumber Any help here will be appreciated. Thanks in advance Dave In news:6BDC4402-6754-45DE-9A34-761EEE988292@microsoft.com, Dave <dave@accessdatapros> wrote: > How can I code a form to close without saving. do not want to even > save the autonumber > > Any help here will be appreciated. You can undo the form before closing it, to prevent the current record from being saved (assuming that it hasn't been saved already): Me.Undo DoCmd.Close acForm, Me.Name, acSaveNo (Note that ...

Move a dialog based application without the title bar
Hi, I have created a dialog based application without a title bar, and want to move the dialog using the left mouse button. I tried MoveWindow nut the window was streching instead of moving !!! Can anyone help me please? >I have created a dialog based application without a title bar, and >want to move the dialog using the left mouse button. >I tried MoveWindow nut the window was streching instead of moving !!! MoveWindow can resize and move the window, so you've got a coding problem. If you use SetWindowPos you can specify the SWP_NOSIZE option to have it ignore the cx & c...

Is there any way to convert a pdf document into an excel document.
Is there any way to convert a pdf file into an excel document? ...

I want to import from EXCEL without making labels or a table. Can.
I'm trying to list names/addresses on a list for a directory. I want to import data from Excel. How can I make the entries in EXCEL display one after the other without creating labels? If I get what your intention is, you will need to a Merge. You will need to setup your page as labels commensurate with the size of the area you want to allocate for each address. Now to get the data in the correct import order, you will need to do a SAVE AS in Excel to a CSV (Comma Separated Values) file and use that for the merge data. bboone wrote: || I'm trying to list names/addresses on a...

limiting permissions for users without using the system.dbw as a user id source
Hi All, i buit a database application for a stand along use so the users only using the file on their desktop is it posible to set permission via access without linkage to the system.mdw? ...

Outlook 2003 / BCM
When you pull up the Opportunities page in Outlook 2003 / BCM - it sorts the stages alphabetically - (e.g. Closed Lost, Negotiation etc.). Is there a way for the view to open with the stages sorted by "stage" - (e.g. Prospecting, Qualification, Needs Analysis, etc.)? Please advise, BSW ...

Thanks
Thanks to all those who responded to my earlier query. No luck so far. I'm still looking for a way to display the present cursor position - at least the line number - in the Status Bar, with the page number, at the bottom of the screen in Office Mac 2008. Alan On 1/20/08 4:28 PM, in article q2Pkj.56763$wx.11480@pd7urf1no, "Alan" <rutherford@hotmail.com> wrote: > Thanks to all those who responded to my earlier query. No luck so far. > I'm still looking for a way to display the present cursor position - at > least the line number - in the Status Bar, with t...

How to logon without domain name?
My daughter at college has her Outlook set up (her school actually set it up) in a way that requires her to logon when the program comes up. Her logon requires not only a name and password, it also requires a domain name. I assume she is simply logging on to their Exchange Server at that point - through their internal network. I am trying to help her be able to use Outlook to also get her EMail through a normal POP3 account on a leased server - not associated with her school. We can't seem to get past the logon screen, however. If she leaves the domain blank it will not allow her to conti...

Outlook moves items without rules..
Hi, I've a very strange problem : we upgrade from NT and outlook 2000 to XP and outlook 2002. A user complains that a new personnal folder ( pst ) appears, and close is disabled.More strange : The messages arrives in the normal mailbox, after read it, the messages moves automatically to the pst file inbox folder. If i move the messages from the pst file inbox folder to the normal inbox folder, the messages comes back automatically to the pst inbox folder. I've checked the rules, there's only one defined to move items from an exception list. But the exception list was emp...