Query Assistance.

I am relatively new to the use of coplex queries. Here is a task that I am trying to accomplish.

Source table.

Address ID Workstation Test-a 1 WS1 Test-b 2 WS1 Test-a 5 WS2 Test-d 3 WS2 Test-b 7 WS2

I am trying to write a query that will display this result into Excel.

Address Duplicate WS1 WS2 Test-a
Yes
1
5 Test-b
Yes
2
7 Test-d
No

Basically I am trying to identify if there is a duplicate address, if so mark it as such in the duplicate column and then placing the ID into a column under the Workstation.
I only want to see the duplicated address once (Distinct?) but mark that it is indeed a duplicate and mark the ID's that it has under the workstations.

Any ideas? I have created a query that does pull the data in the first example that is doing a DTS export to excel. However I need to format this to show the second example.

I appreciate any help I can get on this.

What are the possible values for the Workstation column? Is it always at most 2 workstations for any address?|||No, there are actually around 8-10 workstations.
|||

This should give you an idea about how to approach the solution.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Address varchar(20),
[ID] int,
Workstation varchar(20)
)

INSERT INTO @.MyTable VALUES ( 'Test-a', 1, 'WS1' )
INSERT INTO @.MyTable VALUES ( 'Test-b', 2, 'WS1' )
INSERT INTO @.MyTable VALUES ( 'Test-a', 5, 'WS2' )
INSERT INTO @.MyTable VALUES ( 'Test-d', 3, 'WS2' )
INSERT INTO @.MyTable VALUES ( 'Test-b', 7, 'WS2' )

SELECT
Duplicate = CASE WHEN dt.ADDRESS IS NULL THEN 'No' ELSE 'Yes' END,
m.Address,
m.[ID],
m.Workstation
FROM @.MyTable m
LEFT JOIN (SELECT Address
FROM @.MyTable
GROUP BY
Address
HAVING count( Address ) >= 2
) dt
ON m.Address = dt.Address
ORDER BY
Address,
Workstation

Duplicate Address ID Workstation
-- -- --
Yes Test-a 1 WS1
Yes Test-a 5 WS2
Yes Test-b 2 WS1
Yes Test-b 7 WS2
No Test-d 3 WS2

|||I appreciate the information. I have been working with this to pull the data I need. I have modified the insert statement to pull the data from another table.

I am getting an error however:
Server: Msg 209, Level 16, State 1, Line 15
Ambiguous column name 'PointAddress'.

As I mentioned I am new to alot of this, how do I identify line 15? I have searched on the error itself and found references that I should be creating an alias for the "pointaddress" column.

I appreciate the help with this. Explanations will be helpful as well.

Code Snippet

SET NOCOUNT ON

DECLARE @.dup_trnd table
( RowID int IDENTITY,
PointAddress varchar(30),
TrendID int,
Workstation varchar(30)
)

INSERT dup_trnd
SELECT PointAddress,TrendID,Workstation from DuplicateTrends

SELECT
Duplicate = CASE WHEN dt.PointAddress IS NULL THEN 'No' ELSE 'Yes' END,
m.PointAddress,
m.TrendId,
m.Workstation
FROM @.dup_trnd m
LEFT JOIN (SELECT PointAddress
FROM @.dup_trnd
GROUP BY
PointAddress
HAVING count( PointAddress ) >= 2
) dt
ON m.PointAddress = dt.PointAddress
ORDER BY
PointAddress,
Workstation

|||

Try this:

Code Snippet

SET NOCOUNT ON

SELECT
Duplicate = CASE WHEN dt.PointAddress IS NULL THEN 'No' ELSE 'Yes' END,
d.PointAddress,
d.TrendId,
d.Workstation
FROM Dup_Trnd d
LEFT JOIN (SELECT PointAddress
FROM Dup_Trnd
GROUP BY PointAddress
HAVING count( PointAddress ) >= 2
) dt
ON d.PointAddress = dt.PointAddress
ORDER BY
d.PointAddress,
d.Workstation

|||Thank you for the assistance. This is mostly what I need and can work on it from here.