1 string grouped more rows - 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

SQL Server 2005 Forums

Transact-SQL (2005)

1 string grouped more rows

Author

Topic

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-16 : 09:31:38

Hallo....

Please .....
I need select "min" Commission by group....
Because string I dont know, pleas help me.

TabEmpl
NumEmp...Exctract
l
2
3
4

TabCommission
NumEmpl..Commission...Group
1.........10,00..........A
1.........12,00..........A
1.........5,00...........X
1.........10,00..........X
3.........15,00..........B
3.........20,00..........B
3.........30,00..........C
3.........40,00..........C

RESULT:

TabEmpl
NumEmpl...Extract
1.......A 10, X 5
2.......
3.......B 15, C 30
4.......

big thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-16 : 09:37:20

First we need to see the formatted code...
Hint: You can place [C0DE] [/C0DE] around your formatted text.


Please .....
I need min Commission by group....
Because string I dont know, pleas help me.


TabEmpl               TabCommission
NumEmpl      NumEmpl..Commission...Group
1              1.........10..........A
2              1.........12..........A
3              1.........5...........X
4              1.........10..........X
               3.........15..........B
               3.........20..........B
               3.........30..........C
               3.........40..........C

RESULT:

TabEmpl
NumEmpl...Extract
1.......A 10, X 5
2.......
3.......B 15, C 30
4.......

big thanks

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-16 : 10:26:51

I don't understand your description of "I need min Commission by group" and the result of:
TabEmpl
NumEmpl...Extract
1.......A 10, X 5
2.......
3.......B 15, C 30
4.......

Is Extract a column? If so then do you want to combine Commision and Group into a string and then combine that as a comma separated list with the other MIN Commision and Groups?

Maybe this link will help you prepare your question and data in a way that it'll make it easier for us to help you:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-16 : 10:39:52

yes extract is column in TabEmpl

or simply written:

TabCommission
NumEmpl...Commission...Group
1..........10,00........A
1..........15,00........A
1..........20,00........A
1..........10,00........B
1..........20,00........B

result:

TabEmpl
NumEmpl...Extract
1.....A 10, B 10

select distinct group from TabCommission
where commission is min and +,+
and TabCommission.NumEmpl=TabEmpl.NumEmpl

I know syntax is wrong ... therefore I need help.

thanks all

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-16 : 14:41:55

[code]-- Setuo test data
DECLARE @TabEmpl TABLE (NumEmp INT, Extract VARCHAR(MAX))

INSERT @TabEmpl (NumEmp)
VALUES (1), (2), (3), (4)

DECLARE @TabCommission TABLE (NumEmp INT, Commission MONEY,[Group] CHAR(1))

INSERT @TabCommission (NumEmp, Commission, [Group])
VALUES
(1, 10.00, 'A'),
(1, 12.00, 'A'),
(1, 5.00, 'X'),
(1, 10.00, 'X'),
(3, 15.00, 'B'),
(3, 20.00, 'B'),
(3, 30.00, 'C'),
(3, 40.00, 'C')

-- Update
UPDATE
A
SET
Extract =
STUFF(
(
SELECT DISTINCT TOP 100 PERCENT ', ' + B.[Group] + ' ' + CAST(MIN(B.Commission) AS VARCHAR(30))
FROM @TabCommission AS B
WHERE B.NumEmp = A.NumEmp
GROUP BY B.NumEmp, B.[Group]
ORDER BY ', ' + B.[Group] + ' ' + CAST(MIN(B.Commission) AS VARCHAR(30)) FOR XML PATH('')
), 1, 2, '')
FROM
@TabEmpl AS A

-- Verify Update
SELECT *
FROM @TabEmpl[/code]

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-16 : 15:20:58

thanks lamprey
I will try..... but exist only stored procedure? "select" it can not be?
there is probably no solution selects

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-17 : 11:40:30

quote:
Originally posted by Gekko
I will try..... but exist only stored procedure? "select" it can not be?
there is probably no solution selects

what do you mean by that ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources