Solved

Question about SQL

Posted on 2009-04-11
7
285 Views
Last Modified: 2012-05-06
Hi expert!

Im using SQL Server 2005 and have question. I need to make effective SQL

I have follow table
/****** Object:  Table [dbo].[LPlans]    Script Date: 04/11/2009 09:55:06 ******/
CREATE TABLE [dbo].[LPlans](
      [LPlanID] [bigint] NOT NULL,
      [LTimeSlotID] [bigint] NOT NULL,
      [LCampaignFileID] [bigint] NOT NULL,
      [LCampaignID] [bigint] NOT NULL,
      [SortOrder] [int] NOT NULL,
      [Point] [int] NOT NULL CONSTRAINT [DF_LPlans_Point]  DEFAULT ((0)),
      [Type] [tinyint] NOT NULL CONSTRAINT [DF_LPlans_Type]  DEFAULT ((0)),
      [CB] [int] NULL,
      [CD] [smalldatetime] NULL,
      [LUB] [int] NULL,
      [LUD] [smalldatetime] NULL,
 CONSTRAINT [PK_LPlans] PRIMARY KEY CLUSTERED
(
      [LPlanID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


I need one view, which return Count of LPlanID and list with ID separated with comman for which are this count. I guess about this:

SELECT COUNT(LPlanID) AS Expr1, dbo.fn_GetID(LCampaignFileID) AS Expr2
FROM  dbo.LPlans
WHERE (LCampaignFileID = 2)
GROUP BY dbo.fn_GetID()

And dbo.fn_GetID(LCampaignFileID) is User Defined function in which I loop via record with   CURSOR and manually make return string for example 5,6,9,10 (List with ID of LPlanID)

This will work, but will be slow. Can somebady offer better way ?
0
Comment
Question by:dvplayltd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24121142
I am having difficulty understanding you.  Maybe someone else will better understand you and be able to answer, but you might consider getting someone with better English skills to help you word your question, or just try to be as detailed as possible.
0
 

Author Comment

by:dvplayltd
ID: 24121198
OK, I will try again. I need a view, which return to me count of record and list with this record ID separated with comma . Very simple demand. Here you data which I expect to get like result

  CountLplanID   ListID
2                            3,4

3 and 4 are values of LPlanID , which are counted in first field.
I know that result in kind 3,4 is not common, however I need it.
0
 
LVL 5

Expert Comment

by:QuintainT
ID: 24121237
I don't think you can create the comma seperated list without looping through the records and manipulating a string. If you could post your user defined function that would be useful.

Can you explain why you need to have a comma delimited list in SQL. Is it possible to just select the raw data in SQL and return it to code where you can manipulate it?
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24121250
I believe you are looking for something like the code below.  If you don't really need a view you can just add the where clause to the query used in the view.
HTH
Create view YourView as
SELECT LCampaignFileID, 
       COUNT(LPlanID) as LPlanIDCount, 
       LPlanIDList = REPLACE((SELECT LPlanID AS [data()]
                   FROM dbo.LPlans AS b
                   WHERE b.LCampaignFileID = a.LCampaignFileID
                   FOR XML PATH ('')), ' ', ',')
FROM dbo.LPlans AS a
GROUP BY a.LCampaignFileID
GO
 
SELECT LPlanIDCount, LPlanIDList
FROM  YourView
WHERE (LCampaignFileID = 2)

Open in new window

0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24121277
What you're asking for is certainly not something that an SQL statement is designed to do and it may reflect a poor implementation of whatever process it is that needs these results.

If you can't do what QuintainT suggested, and you need to have SQL give you the results in this format then you should either do the entire process in your function or else in a stored procedure.  To do it in an SQL function would require subqueries which make for very slow SQL statements (and that's basically what you have now with the function you are calling from your SQL).  Instead, use a stored procedure to loop through the records one at a time and create the result as it goes.  This way you only traverse the records one single time and it should be much faster.  I think that stored procedures can have output parameters (sorry, I'm not an expert on them so I'm not positive), but if not then you can just use the stored procedure to create a tempory table that contains 1 record with the two fields you need.

Hope that helps
0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24121278
Edit:  Where I said "To do it in an SQL function..." I meant to say "To do it in an SQL statement..."
0
 

Author Closing Comment

by:dvplayltd
ID: 31569145
Very correct answer to question, thank you!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question