Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: how to write this in T/SQL SQL Server

    Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes. But the SQL needs to use the LAG function for each employee, hence a hundred emps a hundred LAGs, so kind of bulky.

    This example uses a max of 6, and would need more cut n pasting to do more than that.


    SQL> select deptno, dname, emps
    2 from (
    3 select d.deptno, d.dname, rtrim(e.ename ||', '||
    4 lead(e.ename,1) over (partition by d.deptno
    5 order by e.ename) ||', '||
    6 lead(e.ename,2) over (partition by d.deptno
    7 order by e.ename) ||', '||
    8 lead(e.ename,3) over (partition by d.deptno
    9 order by e.ename) ||', '||
    10 lead(e.ename,4) over (partition by d.deptno
    11 order by e.ename) ||', '||
    12 lead(e.ename,5) over (partition by d.deptno
    13 order by e.ename),', ') emps,
    14 row_number () over (partition by d.deptno
    15 order by e.ename) x
    16 from emp e, dept d
    17 where d.deptno = e.deptno
    18 )
    19 where x = 1
    20 /

    DEPTNO DNAME EMPS
    ------- ----------- ------------------------------------------
    10 ACCOUNTING CLARK, KING, MILLER
    20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH
    30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

  2. #2
    Join Date
    Sep 2003
    Posts
    69
    Please re-explain what you are trying... I am sure there is an easy way to do it!

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    Table A has Data:
    -------------------------------------------------------------------------------------------
    MODEL NAME QUANTITY PACKAGE
    -------------------------------------------------------------------------------------------
    model_1 diode 40 POA
    model_2 triode 2 TOS
    model_3 diode 60 POB
    model_4 IC 1100 ICC
    model_5 IC 8900 ICP
    -----------------------------------------------------------------------------------
    Result I want:
    -----------------------------------------------------------------------------------
    NAME TOTAL PACKAGES
    -----------------------------------------------------------------------------------
    diode 100 POA, POB
    triode 2 TOS
    IC 10000 ICC, ICP
    ------------------------------------------------------------------------------------

    Query Currently I used is:
    SELECT NAME, SUM(QUANTITY)
    FROM A
    GROUP BY NAME

    But as you know, I can't fetch the column PACKAGES by use something like SUM(), because of aggregate function only used for numeric types.

    I've searched one document shows how to do this in oracle, but T/SQL syntax was different and is more complex to understand, this is the reason I posted this topic

  4. #4
    Join Date
    Sep 2003
    Posts
    69
    Hopefully I understand your problem.... why not just SELECT the column if you want it and throw it in the GROUP BY? IF you want to add them together for some wierd reason you could assign all the package names numbers (in powers of 2's so you can add them together knowing which combination of packages are included... you would need a bit of logical operations and math to detmine this though...much the same way a lot of function accept multiple flags OR'ed together)

    OR

    Why not change the column "package" to a number, put an index on it (or make it store a foreign key) and then make primary keys in a new table called "tblPackages" which will have two columns:

    PackageID | PackageText
    ------------------------------
    1 | POA
    2 | TOS
    ------------------------------

    Hope this wasn't to confusing! Good Luck!

  5. #5
    Join Date
    Sep 2003
    Posts
    3
    Thanks for your reply, I know your thinking.
    But it seems will not work the best performance as I wish.
    Just think that I need to fetch 100,000 records in one time, How can I make it save most time as User will not accept the technical wait(I mean that it's just because I can't code it to make it faster!).
    what I think is that I can do everything in only one query, not 100,000 times to fetch 100000 records, it's crazy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •