Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    27

    Unanswered: Help Needed In Complex Logic

    Hi,

    My table and data:
    Code:
    Create table Sample(Empid int primary key identity(1,1),EmpName varchar(50),Empsalary  money,EmpDesignation varchar(30));
    
    insert into Sample values('Jhon',8000,'Manager'),
    ('Smith',6000,'Lead'),
    ('Samuel',4000,'AccountExecutive'),
    ('Simson',4000,'AccountSpecialist'),
    ('Eric',22000,'Director'),
    ('Jonathan',12000,'SeniorManager')

    Expected Result:
    Code:
    select  'EmpName','Jhon','Smith','Samuel','Simson','Eric','Jonathan' union all
    select 'Salary','8000','6000','4000','4000','22000','12000' union all
    select 'Designation','Manager','Lead','AccountExecutive','AccountSpecialist','Director','SeniorManager'
    Is it possible to do without using loop? can anyone please give me some sample query to achieve. Also
    I have 2k * 1000 reocords in my table. Is it possible to bring the data based on by passing the page size.

    Thanks in Advance

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Do you really have only one row in the NON-table as you said. There is no key and no way to have a key. The IDENTITY table property is not a column or a data type. What math do you do with emp_id? I hope, the answer is none. Identifiers are strings.

    Do you know that we never use MONEY? The math does not work!! It has a precision that is illegal in many cases, etc.

    The USPS has 35 character per addresses line, but you picked 50! You used ACCESS defaults and did no research.

    An employee has a relationship with a job title, but you think it is an attribute of him. Why? The salary is also part of the job, not the employee.

    CREATE TABLE Samples
    (emp_id CHAR(10) NOT NULL PRIMARY KEY,
    emp_name VARCHAR(35) NOT NULL,
    emp_salary DECIMAL (12, 2) NOT NULL
    CHECK (emp_salary >= 0.00) ,
    emp_designation VARCHAR(30) NOT NULL);

    INSERT INTo Samples
    VALUES('134567890', 'John', 8000.00, 'Manager'),
    ('134567891', 'Smith', 6000.00, 'Lead'),
    ('134567892', 'Samuel', 4000.00, 'Account Executive'),
    ('134567893', 'Simson', 4000.00, 'Account Specialist'),
    ('134567894', 'Eric', 22000.00, 'Director'),
    ('134567895', 'Jonathan', 12000.00, 'Senior Manager');

    The rest of the posting makes no sense.

    >> Is it possible to do without using loop? <<

    SQ is declarative, so it has no loops

    >> can anyone please give me some sample query to achieve. Also I have 2k * 1000 records [sic: rows are not records; this is fundamental!] in my table. <<

    So far, you have done nothing right.

    >> Is it possible to bring the data based on by passing the page size. <<

    What is a page?? More nonsense! SQL returns a set, a result table. It does not format data. That was COBOL. A presentation layer will handle physical pagination.

  3. #3
    Join Date
    Apr 2011
    Posts
    27
    Hi Celko,

    Thanks for your time on this, below is my try
    Code:
    SELECT *
    FROM
    (
    select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS Rn
    FROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)s
    unpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u
    )m
    PIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p
    ORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END
    But i have half million records in my table and if i need to select Top 10000, i need to make this as dynamic. Any help on making it to dynamic sql please

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> But I have half million records [sic] in my table and if I need to SELECT TOP 10000, I need to make this as dynamic. <<

    Again, rows are not records; this is a fundamental concept in SQL and RDBMS. TOP is a proprietary “feature” that will never port; but you magnetic tape file programmers can use it to mimic a loop in a COBOL program. Unfortunately, we have no idea what to do with duplicates or even what we are sorting with.

    Dynamic SQL is how you tell the world that your programing is so bad that you cannot build a valid data model.

    Why are you casting numeric values to long strings? Think about about how silly and awful this is! Who has a fifty digit salary?

    Why do you think that “val” and “cat” are clear, precise names that follow ISO-11179 rules? Is this “cat” an animal? Or did you mean “<something>_category” and not know how to write the SQL?

    Why write CAST(emp_designation AS VARCHAR(50)) when the job title ought to be a (much shorter) character string already?

    Please quit being rude and post some DDL and specs. Was my guess right??

Posting Permissions

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