Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL 2000 or SQL 2005

Posted on 2014-01-10
23
502 Views
Last Modified: 2014-01-13
How can I output  the following format in SQL using the Group By
Function in the following ORDER  I want the company then the people under.
I have 2 tables tComapny  and t Employee.

JCSouth
Smith  John
Houston Steve

JCNorth
Jones Adam
Manning Blake

JCWest
Thomas Wilbert
Jackson Brian

JCEast
Alex Green
Bob Yeager
0
Comment
Question by:smares323
  • 11
  • 6
  • 3
  • +3
23 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39772021
So ... are the JC[North|South|West|East} and the people different columns?

If yes, it would be something like..

ORDER BY Region, CustomerLastName, CustomerFirstName

btw in your mockup data  JCEast has firstname lastname, and all the others have lastname firstname.
0
 

Author Comment

by:smares323
ID: 39772051
Different tables, If I group them they Output
ID  Company    LastName  FirstName
1    JCSouth       Smith          John
2    JCSouth       Houston     Steve
3    JCNorth       Jones           Adam

I want the following Output

1  JCSouth
2  Smith    John
3   Houston  Steve
4  JCNorth
5  Jones Adam
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39772093
Looking at the bottom sample data, are you really (I mean really, c'mon now...) sure that's what you want?

Most reports will receive a set looking like this, which will allow any reporting / app to sort by Company and/or Customer, group with headers for Company, and even expand/collapse customers for a given company:  
 
CompanyID   CompanyName  CustomerID  CustomerName
1                   JCSouth                42                 Smith, John
1                   JCSouth                99                 Houston, Steve
2                   JCNorth                86                 Smart, Max
2                   JCNorth                101               Jones, Adam
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:smares323
ID: 39772099
That's what they want can't argue with them.  I would have been done.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39772226
I would recommend trying (arguing with them I mean) because this request is ridiculous.  I deal with "customer" requests all the time, usually internal, and in most cases it requires a conversation beyond the initial request.  Non-database people simply don't understand how data is stored and retrieved.  The grouping that you display in your requested output should be handled by a reporting layer (SSRS, Crystal) and not in the query.  Can it be done in TSQL? Yes, but it's going to be a maintenance and performance mess.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39772259
Can you provide the actual table layouts?  It's impossible to write a query without ANY knowledge of the schema.
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39772345
If you truly want something like this, you'll need to use UNIONs, something like:

select CAST("JCSouth" as varchar(100)) as Region, 1 AS SortVal
UNION
select LastName + " " + FirstName, 2 AS SortVal
from <table>
where Region = 'JCSouth"
UNION
select ....

With and ORDER BY at the very end like:
ORDER BY SortVal

Not pretty and against all principle of developer decency.
0
 

Author Comment

by:smares323
ID: 39773310
Table schema

Table1

Orgid
Orgname

Table2

Empid
Orgid
Lastname
Firstname
0
 

Author Comment

by:smares323
ID: 39773321
Also there is like 80000 records. 20000 of them are Orgid and 60000 are Empid so Shaun Kline logic would never work. I basically want the layout Orgname then employees in a row each and continue the loop.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39773846
check this.
;with cte as (
select t1.OrgID,t1.OrgName,t2.EmpID,t2.LastName+','+t2.FirstName CustomerName
  from Table1 t1
  join Table2 t2
    on t1.OrgID = t2.OrgID),
cte1 as (
select row_number() over (order by OrgID,EmpID) rn,
       OrgName
 from (
select OrgID,0 EmpID,OrgName from Table1
union all
select OrgID,EmpID,CustomerName 
  from cte) t1)
select * from cte1
order by rn

Open in new window

http://sqlfiddle.com/#!3/818c8/11
0
 

Author Comment

by:smares323
ID: 39777168
Sharath I didn't think this was going to be so complicated the actual schema is below.


select org.organizationID,org.organizationname,tAssignment.assignmentid,tPerson.LastName,tPerson.FirstName CustomerName
  FROM tOrganization org
INNER JOIN tOrganizationType ON org.OrganizationTypeID = tOrganizationType.OrganizationTypeID
INNER JOIN tAssignment ON tAssignment.organizationID = org.organizationID
INNER JOIN tPerson ON tAssignment.personID = tPerson.PersonID
INNER JOIN tAssignmentType ON tAssignment.AssignmentTypeID = tAssignmentType.AssignmentTypeID
INNER JOIN tPersonType ON tPerson.PersonTypeID = tPersonType.PersonTypeID
WHERE  org.parentID is null and org.organizationname is not null and org.organizationname <> '' and (tAssignment.topFirmAssignment = 0))
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39777210
I'm going to agree with BriCrowe on this one.  You're not a waitress dude, so just because the client says 'I want it to look like this' doesn't mean that it SHOULD look like that, or that the design has to exactly match the cosmetics.

With that, I'll unmonitor this question.
Good luck.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39777294
To me the easiest way to do this is to dump the results into an indexed temp table, add the needed extra rows to the table, then the final SELECT is relatively straightforward.

Something like below.


DECLARE @force_unique_index_name varchar(36)
DECLARE @sql varchar(8000)

IF OBJECT_ID('tempdb.dbo.#result') IS NOT NULL
    DROP TABLE #result
CREATE TABLE #result (
    organizationID int, organizationname varchar(100),
    assignmentid int,
    LastName varchar(100), FirstName varchar(100)    
    )
SET @force_unique_index_name = NEWID()
SET @sql = 'CREATE CLUSTERED INDEX [result__CL_' + REPLACE(@force_unique_index_name, '-', '') + '] ON #result ( organizationID, assignmentid ) WITH ( FILLFACTOR = 85 )'
EXEC(@sql)

--individual rows of report
INSERT INTO #result
SELECT
    org.organizationID, org.organizationname,
    tAssignment.assignmentid,
    tPerson.LastName, tPerson.FirstName CustomerName
FROM tOrganization org
INNER JOIN tOrganizationType ON org.OrganizationTypeID = tOrganizationType.OrganizationTypeID
INNER JOIN tAssignment ON tAssignment.organizationID = org.organizationID
INNER JOIN tPerson ON tAssignment.personID = tPerson.PersonID
INNER JOIN tAssignmentType ON tAssignment.AssignmentTypeID = tAssignmentType.AssignmentTypeID
INNER JOIN tPersonType ON tPerson.PersonTypeID = tPersonType.PersonTypeID
WHERE
    org.parentID IS NULL AND
    org.organizationname IS NOT NULL AND
    org.organizationname <> '' AND
    tAssignment.topFirmAssignment = 0

--add company "header" rows to report
INSERT INTO #result ( organizationID, organizationname, assignmentid )
SELECT DISTINCT organizationID, organizationname, NULL
FROM #result
ORDER BY organizationID, organizationname

SELECT
    CASE WHEN assignmentid IS NULL THEN organizationname ELSE CustomerName + LastName END AS [Name]
    --,...
FROM #result
ORDER BY
    organizationID, assignmentid
0
 

Author Comment

by:smares323
ID: 39777435
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

(86947 row(s) affected)

(33674 row(s) affected)

(120621 row(s) affected)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39777455
I don't get any syntax errors when I run the code.  Can you at least copy the statement getting the error here so I can see it??
0
 

Author Comment

by:smares323
ID: 39777468
Error is coming from

DECLARE @force_unique_index_name varchar(36)
DECLARE @sql varchar(8000)

IF OBJECT_ID('tempdb.dbo.#result') IS NOT NULL
    DROP TABLE #result
CREATE TABLE #result (
    organizationID int, organizationname varchar(128),
    assignmentid int,
    LastName varchar(100), FirstName varchar(100)    
    )
SET @force_unique_index_name = NEWID()
SET @sql = 'CREATE CLUSTERED INDEX [result__CL_' + REPLACE(@force_unique_index_name, '-', '') + '] ON #result ( organizationID, assignmentid ) WITH ( FILLFACTOR = 85 )'
EXEC(@sql)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39777484
I can execute that code fine on my box ... maybe you have some other code before it??!
0
 

Author Comment

by:smares323
ID: 39777526
I'm running SQL 2000
0
 

Author Comment

by:smares323
ID: 39777545
Error is coming from:

EXEC(@sql)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39777552
I don't see anything that should be a syntax error on SQL 2000 either.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39777562
Something else is going on ... that is not a syntax error.
0
 

Author Comment

by:smares323
ID: 39777584
It worked when I commented out EXEC(@sql)
0
 

Author Closing Comment

by:smares323
ID: 39777585
Genius great work
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Check ALL SP in database make sure there are no errors 17 43
MS SQL: Create User Function to Remove Long Words 5 31
SQL query and VBA 5 45
TSQL XML Namespaces 7 22
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question