smares323
asked on
SQL 2000 or SQL 2005
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
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
ASKER
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
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
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
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
ASKER
That's what they want can't argue with them. I would have been done.
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.
Can you provide the actual table layouts? It's impossible to write a query without ANY knowledge of the schema.
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.
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.
ASKER
Table schema
Table1
Orgid
Orgname
Table2
Empid
Orgid
Lastname
Firstname
Table1
Orgid
Orgname
Table2
Empid
Orgid
Lastname
Firstname
ASKER
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.
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
http://sqlfiddle.com/#!3/818c8/11
ASKER
Sharath I didn't think this was going to be so complicated the actual schema is below.
select org.organizationID,org.org anizationn ame,tAssig nment.assi gnmentid,t Person.Las tName,tPer son.FirstN ame CustomerName
FROM tOrganization org
INNER JOIN tOrganizationType ON org.OrganizationTypeID = tOrganizationType.Organiza tionTypeID
INNER JOIN tAssignment ON tAssignment.organizationID = org.organizationID
INNER JOIN tPerson ON tAssignment.personID = tPerson.PersonID
INNER JOIN tAssignmentType ON tAssignment.AssignmentType ID = tAssignmentType.Assignment TypeID
INNER JOIN tPersonType ON tPerson.PersonTypeID = tPersonType.PersonTypeID
WHERE org.parentID is null and org.organizationname is not null and org.organizationname <> '' and (tAssignment.topFirmAssign ment = 0))
select org.organizationID,org.org
FROM tOrganization org
INNER JOIN tOrganizationType ON org.OrganizationTypeID = tOrganizationType.Organiza
INNER JOIN tAssignment ON tAssignment.organizationID
INNER JOIN tPerson ON tAssignment.personID = tPerson.PersonID
INNER JOIN tAssignmentType ON tAssignment.AssignmentType
INNER JOIN tPersonType ON tPerson.PersonTypeID = tPersonType.PersonTypeID
WHERE org.parentID is null and org.organizationname is not null and org.organizationname <> '' and (tAssignment.topFirmAssign
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.
With that, I'll unmonitor this question.
Good luck.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
Line 1: Incorrect syntax near '('.
(86947 row(s) affected)
(33674 row(s) affected)
(120621 row(s) affected)
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??
ASKER
Error is coming from
DECLARE @force_unique_index_name varchar(36)
DECLARE @sql varchar(8000)
IF OBJECT_ID('tempdb.dbo.#res ult') 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_inde x_name, '-', '') + '] ON #result ( organizationID, assignmentid ) WITH ( FILLFACTOR = 85 )'
EXEC(@sql)
DECLARE @force_unique_index_name varchar(36)
DECLARE @sql varchar(8000)
IF OBJECT_ID('tempdb.dbo.#res
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_inde
EXEC(@sql)
I can execute that code fine on my box ... maybe you have some other code before it??!
ASKER
I'm running SQL 2000
ASKER
Error is coming from:
EXEC(@sql)
EXEC(@sql)
I don't see anything that should be a syntax error on SQL 2000 either.
Something else is going on ... that is not a syntax error.
ASKER
It worked when I commented out EXEC(@sql)
ASKER
Genius great work
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.