|
SilimSayo wrote: Most of our imports come from abroad!
Imports become necessary when locals lack quality and become unaffordable!
|
|
|
|
|
here is my problem.
table: Employees
id name position
----------------------------------
1 bob sales
2 joe sales
3 mark management
4 jane management
5 julie it
6 donald sales
what I want to do is write a query that would spit out
position people
------------------------------
sales bob, joe, donald
management mark, jane
it julie
so something like this
SELECT position, somefunction(names) AS people FROM Employees GROUP BY position
does that 'somefuction' exist? if not how would i emulate it.
I'm doing this for reporting purposes.
|
|
|
|
|
nallelcm wrote: here is my problem.
I included a script that generates your demo-data; just copy and paste to give it a try.
BEGIN TRANSACTION
CREATE TABLE Employees
(
id INT
,[name] VARCHAR(50)
,position VARCHAR(50)
);
INSERT INTO Employees (id, [name], position)
SELECT 1, 'bob', 'sales'
UNION SELECT 2, 'joe', 'sales'
UNION SELECT 3, 'mark', 'management'
UNION SELECT 4, 'jane', 'management'
UNION SELECT 5, 'julie', 'it'
UNION SELECT 6, 'donald', 'sales'
SELECT DISTINCT position
INTO #Positions
FROM Employees;
SELECT position,
SUBSTRING(names, 0, LEN(names) - LEN(', '))
FROM (SELECT position,
REPLACE(REPLACE((SELECT [name]
FROM employees e
WHERE e.position = p.position
FOR XML AUTO), '<E name="', ''), '"/>', ', ') AS
names
FROM #positions p) AS tmp_cte;
ROLLBACK
Output on my machine;
position names
--------------- ------------------
it julie
management mark, jane
sales bob, joe, donald
Bastard Programmer from Hell
|
|
|
|
|
Ok, well how well would this translate into this situation. I never really did explain myself very well..
here is a better example
EventType
ID Name
-----------------
1 Party
2 Meeting
Person
ID Name
------------
1 Bob
2 Joe
3 Suzie
Event
UID EventID EventType Person
-------------------------------------------------------
1 1 1 1
2 1 1 2
3 1 1 3
4 2 1 1
5 2 1 3
6 3 2 1
7 3 2 2
Want the output to look like
EventID EventTypeName People
-------------------------------------------------------
1 Party Bob, Joe, Suzie
2 Party Bob, Suzie
3 Meeting Bob, Joe
|
|
|
|
|
nallelcm wrote: here is a better example
That's not a better example, but a different one. It would have been better if it included a script to generate the sample data
nallelcm wrote:
Want the output to look like
I want food.
Back in a bit
Bastard Programmer from Hell
|
|
|
|
|
The same trick, basically;
BEGIN TRANSACTION
CREATE TABLE #EventType
(
ID INT
,[Name] VARCHAR(50)
)
INSERT INTO #EventType
SELECT 1 ,'Party'
UNION SELECT 2 ,'Meeting'
UNION SELECT 3 ,'Something else that wasn''t mentioned'
CREATE TABLE #Person
(
ID INT
,[Name] VARCHAR(20)
)
INSERT INTO #Person
SELECT 1 ,'Bob'
UNION SELECT 2 ,'Joe'
UNION SELECT 3 ,'Suzie'
CREATE TABLE [#Event]
(
UID INT
,EventID INT
,EventType INT
,Person INT
)
INSERT INTO [#Event]
SELECT 1, 1, 1, 1
UNION SELECT 2, 1, 1, 2
UNION SELECT 3, 1, 1, 3
UNION SELECT 4, 2, 1, 1
UNION SELECT 5, 2, 1, 3
UNION SELECT 6, 3, 2, 1
UNION SELECT 7, 3, 2, 2
SELECT DISTINCT EventId
INTO #SomeTable
FROM [#Event]
SELECT EventId
,[Name]
,SUBSTRING(CompoundColumn, 0, LEN(CompoundColumn) - LEN(', '))
AS People
FROM (
SELECT ST.EventId
,ET.[Name]
,REPLACE(REPLACE(
(SELECT P.[Name]
FROM #Event E
JOIN #Person P ON E.Person = P.ID
WHERE E.EventID = ST.EventID
FOR XML AUTO),
'<P Name="', ''), '"/>', ', '
) AS CompoundColumn
FROM #SomeTable ST
LEFT JOIN #EventType ET ON ST.EventId = ET.ID
) AS tmp_cte
ROLLBACK
Desired result
EventID EventTypeName People
-------------------------------------------------------
1 Party Bob, Joe, Suzie
2 Party Bob, Suzie
3 Meeting Bob, Joe
Result on my machine
EventId Name People
------- --------------------------------------- -----------------
1 Party Bob, Joe, Suzie
2 Meeting Bob, Suzie
3 Something else that wasn't mentioned Bob, Joe
Are you sure that your sample output is correct?
Bastard Programmer from Hell
|
|
|
|
|
Thank you!!
in the Event the EventID is a UID for the specific event. EventType is a FK to the EventType table.
So there should be 2 parties (event id 1 and 2) and 1 meeting (event id 3)
I need to look up how this XML stuff works :/
|
|
|
|
|
My pleasure
|
|
|
|
|
Hi All,
table
-----
NAme Age Total
---- --- ------
Ram 26 800
kumar 36 300
Sam 34 200
output should be
------
Name:Ram Age:26 Total:800 ;Name:Kumar Age:36 Total:300;Name.....
Please help me with this .tried using COALESCE
but I could not achieve..
Ramkumar
("When you build bridges you can keep crossing them. ")
http://ramkumarishere.blogspot.com
|
|
|
|
|
You can do it like this...
DECLARE @String VARCHAR(8000)
set @String = ''
SELECT @String = @String + 'Name:' + ISNULL(Name,'NULL') + ' Age:' + Age + ' Total:' + Total + '; '
FROM TABLE
print @String
|
|
|
|
|
Why would you want the data in that format? If you happen to have 1000 records, would want all that data on one line?
|
|
|
|
|
Hi....
Try This....
DECLARE @Temp TABLE(Name VARCHAR(20),Age TINYINT, Total INT)
INSERT INTO @Temp(Name,Age,Total)
SELECT 'Ram',26,800
UNION ALL
SELECT 'kumar',36,300
UNION ALL
SELECT 'Sam',34,200
DECLARE @SQLStr VARCHAR(8000)
SET @SQLStr = ''
SELECT @SQLStr = @SQLStr + 'Name:' + Name + ' Age:' + CAST(Age AS VARCHAR) + ' Total:' + CAST(Total AS VARCHAR) + '; '
FROM @Temp
PRINT @SQLStr
|
|
|
|
|
Im problem is like this.
Im getting these exceptions when running my application.
"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)"
after this exception
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
In mssql server machine SQl server browser is also enabled and firewall is also disabled.
Please help.
Reply·Email·Vi
|
|
|
|
|
Hi,
Please enable the remote connection option..
|
|
|
|
|
|
hi,
i'm trying to create a program like notepad using visual basic.
all of text body inside my note will be save in one cell in Sql Server Management Studio R2, but the max of varchar is varchar(8000), and there's varchar(MAX) too.
nah if varchar(MAX) is bigger than 8000, may i know how many character it could save?
i'm trying to understand this because i don't want any error caused by character overload.
thankyou before. =)
|
|
|
|
|
Instead of typing the question in here you could have done a search[^] on the keywords and caroused the results, they look quite useful to me.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
wow, thankyou for sharing that link.
pretty cool way to tell someone to search..
|
|
|
|
|
Actually it's rather nasty but it does get the point across. There are guidelines at the top which give you the same information in a more polite fashion. You, like the rest of us, obviously don't read the manual and just dived right in, most newbies get introduced to LMGTFY.
Anyway welcome to the site, may your questions be clear and interesting.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
btw, varchar(max) indicates that the maximum storage size is 2^31-1 bytes
is that means 2,147,483,648 - 1 become 2,147,483,647 characters?
and then i don't have to worry about character overload anymore?
|
|
|
|
|
I should think that will be adequate for a NotePad style application
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
okay, thankyou very much bro, god bless you..
|
|
|
|
|
vkstarry wrote: the max of varchar is varchar(8000)
Yup, anything longer than that would cripple the database-index when you do a LIKE operation.
To keep it simple; the documents contents should be a varchar(max) (or nvarchar if you need to store unicode), and you should use strings that are limited in length to describe other properties (like a title).
Bastard Programmer from Hell
|
|
|
|
|
I want to add one column in sql table.
That will be in specific format. The format is like that
Year+00001
Year+00002
....and so on. If the year changes to new year, I want to start from 00001. like that.
Year+00001
Year+00002
to be more clear what I need is: like below in applicationID column.
name | date | applicationID
Willam | 2011-12-03 | 201100001
Susan | 2011-12-04 | 201100002
Alex | 2012-01-01 | 201200001
Mata | 2013-01-01 | 201300001
how I have to do. &
please.
|
|
|
|
|
I suspect this is a BAD idea, I also suspect you are using this field as a primary key. If so the DON'T it is a BAD design.
To do this you will need to query the table to find out how many inserts there are for this year, build your string and insert the record. There are a number of alternatives to this process that include triggers and stored procedures.
Here is the reason it is a bad idea. What happens if another user inserts a record during this process, BOOM one busted database.
I suggest you insert the datetime for Created then query filtered on the year and ordered by the created date. If you need an incremental number you can use ROW_NUMBER() assuming you are using sql server
Never underestimate the power of human stupidity
RAH
|
|
|
|
|