|
Try this
declare @t table(Emp_Name varchar(50))
insert into @t select 'A' union all select 'B' union all select 'C'
union all select 'D' union all select 'E'
Solution 1:
declare @concat varchar(100)
set @concat = ''
select @concat = @concat + Emp_Name + ',' from @t
select Emp_Name =Left(@concat,len(@concat)-1)
Solution 2:
select Emp_Name = RIGHT(delimitedCol,len(delimitedCol)-1) from
(select ',' + CAST(Emp_Name AS varchar(8000))
from @t
for xml path(''))x(delimitedCol)
Output(For both the cases)
Emp_Name
A,B,C,D,E
Even you can use while loop , cursors but the approaches mentioned above are faster enough.
 Niladri Biswas
|
|
|
|
|
Can i know the reason why you want to use Pivot?
Using For XML PATH('') also you can get the same result.
select emp_name + ', ' from tabble where condition for xml path('')
Regards
NainaNaina
|
|
|
|
|
in my data base in the order table there is a status field and it has many different values relevant to orders and order table has a relationship with customers table on customercode, what i want is to get the count of each order status relevant to each customer.any idea how
|
|
|
|
|
Look into the group by clause.
Select count(*), Customer, Status
From Table
Group By Customer, Status Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to display a schedule in a Gantt chart, MS Project-like, horizontal stacked bar chart.
I embedded an unbound OLE object on my form, selected MS Graph Chart, and defined Row Source = qry_MySched, Row Source Type = Table/Query.
What I am seeing is my rows/columns are transposed and I a missing a row of data that should display.
Here is my data:
tbl_Schedule (ID, StartDay, Duration)
1, 1, 3
2, 6, 5
3, 7, 4
qry_MySched (SQL format)
SELECT "Event " & [ID] AS Label, [StartDay]-1 AS [Begin], tbl_Schedule.Duration
FROM tbl_Schedule;
Resulting in the query:
Event 1, 0, 3
Event 2, 5, 5
Event 3, 6, 4
My stacked bar chart then has
(Y-axis): 3, 0
(legend): Event 2, Event 3
(horizontal bar for "3") 0-5 (5units), Event2; 5-9 (4units), Event 3
(horizontal bar for "0") 0-5 (5units), Event2; 5-11 (6units), Event 3
Working with MS Graph Chart difficult in Access 2007. (Access 2007, WinXP, SP3)
Thanks for any help,
JJ
|
|
|
|
|
Hi..
I have the scene like this....
I have one master table say Users(userid, pwd) and child table customer(userid,name,address)
both tables are related with field 'userid'.
Now customer wants to update his userid.
so userid will be updated in master as well as child record.
could u suggest me the update query for this without deleting and inserting the records.
thanks,
Hemant
|
|
|
|
|
| Your basic design is wrong, the userid should not be significant and should not be the primary key if it can be changed. However, if this is an existing system which you cannot change then simply do the update to both tables within a single transaction - assuming you do not have a foreign key constraint. Bob
Ashfield Consultants Ltd
|
|
|
|
|
thanks for reply,
i do have foreign key which is userid in customer table.
will it be possible to write a single statement for update.
could u guide.,
thanksBy:
Hemant Thaker
|
|
|
|
|
I think on update cascade will work here, what do u say ?
thanksBy:
Hemant Thaker
|
|
|
|
|
You should immediately remedy the primary key issue if you have control of the database. I would add an identity field UserNo to user table and the appropriate fields and keys to the FK table(s).
As Bob said you have made a fundamental error in data design, fix it and you no longer have the update problem.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I use SQL Server 2005. Case is
Valsue in Totall field are: -2, 5,4,-1
Select sum(Total) as sTotal from Table1.
it gives 6.
i want to sum positive and negative values seperatly
that is
Select sum(Total) as PositiveSum, Sum(total) as NegetiveSum as sTotal from Table1.
|
|
|
|
|
use case when statement
like
sum(case when colvalue<0 then colvalue end) as [Total negative],
sum(case when colvalue>=0 then colvalue end) as [Total positive],
|
|
|
|
|
Use a case statement
Sum(Case when ValueField > 0 then ValueField else 0 end) SumPositive,
Sum(Case when ValueField < 0 then ValueField else 0 end) Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Even you can try this
declare @t table(field int)
insert into @t select -2 union all select 5 union all select 4
union all select -1
select top 1
PositiveSum = (select SUM(field) from @t where field >0)
,NegetiveSum =(select SUM(field) from @t where field <0)
from @t
Output:
PositiveSum NegetiveSum
9 -3
 Niladri Biswas
|
|
|
|
|
Hi, I want to list cares sold to buyers from two tables :
BuyersTable & ProductsTable
I want to get below result :
Buyer Product
Tom Benz
Bmw
Volvo
Jim Bmw
Benz
Tanks for help.
|
|
|
|
|
You are looking at a simple inner join here between a few tables. I would assume your lecturer covered this.
It will be in the form of:
Select BuyerName, ProductName
from tblBuyer
inner join tblBuyerProduct on BuyerID = BP_BuyerID
inner join tblProduct on BP_ProductID = ProductID
order by BuyerName, ProductName
This assumes you have a standard table setup - tblBuyer (for buyer details), tblProduct (for Product Details) and tblBuyerProduct to create a many to many link between Buyers and Products.
|
|
|
|
|
Dear Friend
Thank you for reply
But i want to list products with none repeatitve buyers, all queries list data and repeat buyer's name at each row, I want result like below :
Buyer Product
Jack BMW
Benz
Volvo
Tom Benz
Toyota
Ed BMW
Volvo
Citruen
Please help...
|
|
|
|
|
That becomes a formatting issue... how are you displaying the data to the users?
|
|
|
|
|
Hi
It's good point, I want to show data in Crystal Report. Do you know any tutorial ?
|
|
|
|
|
I have sql statement in access database to find records -3 fields ID, name, and Date.
i use
SELECT ID,COUNT(name)AS CNT
FROM Table1
WHERE (((Table1.Date)=[Enter Date]
GROUP BY ID
HAVING COUNT(name)>1
now I want to find records < 1 - not sure of how to start to find these-any ideas-
There are supposed to be 30 records each week added - sometimes people add too many records ( the statement above helps- but if they did not add record- do not know how to figure that)
Thank you,
|
|
|
|
|
Because it's a single table, you are a little restricted in how you can do this...
One way is to use the following:
SELECT ID
FROM Table1
WHERE ID not in(
SELECT ID
FROM Table1
WHERE ((Table1.Date)=[Enter Date]
GROUP BY ID
HAVING COUNT(name)>1
)
Naturally this assumes that your existing query is returning the details you are after... Note also that your original query is only returning duplicates... If you want to return records where there is a single entry, your original query needs:
HAVING COUNT(name) >= 1
|
|
|
|
|
|
I work with a database (SQL Server 2000) with thousands of stored procedures. In order to group them so that they are easier to manage, we have made use of numbered stored procedure groups (e.g., "SomeSPGroup;1", "SomeSPGroup;2", and so on). That's better than nothing, but still it's not so great. We have one SP group with hundreds of SP's in it. What I would really like is some way to group them into a namespace and nest further namespaces in that namespace. I can sort of do this now by naming them with dot notation, but then it becomes somewhat clumsy to use them. For example, you have to type out the full name and SQL Server Management Studio provides no recognition that these SP's are related, so they are all just listed next to the other SP's in the tree view, rather than giving them their own node that can be expanded. For example, I could name two SP's like so:
[OrderProcessing.CreateOrder]
[OrderProcessing.DeleteOrder]
Whenever I want to execute those, I have to use the full names:
EXEC [OrderProcessing.CreateOrder] @param1, @param2
And the list of SP's in SSMS shows them side by side with other SP's, like this:
...
MakeShipment
[OrderProcessing.CreateOrder]
[OrderProcessing.DeleteOrder]
ProcessPayment
...
What I would like to see is a hierarchy:
...
MakeShipment
-OrderProcessing <-- This node can expand and collapse.
CreateOrder
DeleteOrder
ProcessPayment
...
I suppose I could create my own tool or perhaps make a plugin that will do this for me, but there is still no support for namespaces in the actual SQL code (i.e., I have to use the whole name when executing it).
What I would like to hear is any thoughts you have on this. Do you know of any database provider that currently has a concept of namespaces? Have you header of Microsoft planning support for this in a future version of SQL Server? Does it already exist in SQL Server and I've just missed it somehow? Do you have alternative methods for managing large groups of stored procedures (and, no, prefixes are not an appealing option to me)?
|
|
|
|
|
A Schema is somewhat equivalent to a namespace in this context.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Interesting. From what I can see, the only advantage a schema has over naming an SP with dots in it is that the square brackets are not required around the full name (e.g., I can say dbo.SomeSP instead of [dbo.SomeSP]). Would be nice if schemas could be nested, could be contextualized (e.g., using dbo; would remove the need to prefix with that schema in that scope), and had tooling support (e.g., if SSMS showed schemas in the tree view and allowed them to be collapsed/expanded to show the objects in them). I suppose they also have other advantages (applying permissions to the schema gives the same permissions to the objects it contains?), but none organize SP's in the manner I'm thinking of. Thanks for the info though.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
