Using UNION to generate view with NULL AS - Jean

I am trying to create a view using the tables Application,
Application_mm_User, User and Role.

Application:
AppID (PK)
AppName

Application_mm_User:
AppUserID (PK)
AppID (FK)
UserID (FK)
RoleID (FK)

User:
UserID (PK)
DisplayName

Role:
RoleID (PK)
RoleName

I have the following definition for my view:

SELECT     Application.AppName, [User].DisplayName AS Admin, NULL AS
PowerUser, NULL AS EndUser
FROM  Application INNER JOIN
Application_mm_User ON Application.AppID =
Application_mm_User.AppID INNER JOIN
[User] ON Application _mm_User.UserID = [User].UserID INNER
JOIN
Role ON Application_mm_User.RoleID = Role.RoleID
WHERE     (Role.Name = N'Admin')
UNION
SELECT     Application.AppName, NULL AS Admin, [User].DisplayName AS
PowerUser, NULL AS EndUser
FROM         Application INNER JOIN
Application_mm_User ON Application.AppID =
Application_mm_User.AppID INNER JOIN
[User] ON Application _mm_User.UserID =
[User].UserID INNER JOIN
Role ON Application_mm_User.RoleID =
Role.RoleID
WHERE     (Role.Name = N'PowerUser')
SELECT     Application.AppName, NULL AS Admin, NULL AS PowerUser,
[User].DisplayName AS EndUser
FROM         Application INNER JOIN
Application_mm_User ON Application.AppID =
Application_mm_User.AppID INNER JOIN
[User] ON Application _mm_User.UserID =
[User].UserID INNER JOIN
Role ON Application_mm_User.RoleID =
Role.RoleID
WHERE     (Role.Name = N'EndUser')


However, the results I get are not what I want: I would like to have
the DisplayName for each AppName on one line, but presently there are
3 lines generated for each AppName, and the columns contain NULL.

How can I get the NULL values to be replaced by the DisplayName and
display on 1 line?

Many thanks in advance!

Using UNION to generate view with NULL AS - Jean

BTW, I am using SQL 2005 and have been looking at using the new PIVOT
operator - however, I do not need to aggregate anything (e.g. using
SUM or count)...

Using UNION to generate view with NULL AS - Roy Harvey (SQL Server MVP)

First, since the three alternatives must be different UNION ALL will
be a bit more efficient than simple UNION.  UNION will do a DISTINCT.

One approach:

SELECT AppName,
MAX(Admin) as Admin,
MAX(PowerUser) as PowerUser,
MAX(EndUser) as EndUser
FROM (<your UNION ALL query here)
GROUP BY AppName

Roy Harvey
Beacon Falls, CT

On Wed, 07 Nov 2007 02:11:24 -0800, Jean <jeanjanssens@hotmail.com>

Using UNION to generate view with NULL AS - AlejandroMes

Jean,


You can also use MIN and MAX functions.

DECLARE @t TABLE (
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL
)

INSERT INTO @t VALUES(1, 10, 100)
INSERT INTO @t VALUES(1, 20, 200)
INSERT INTO @t VALUES(1, 30, 300)

INSERT INTO @t VALUES(2, 10, 120)
INSERT INTO @t VALUES(2, 20, 210)
INSERT INTO @t VALUES(2, 30, 330)

SELECT
*
FROM
@t
PIVOT
(
MIN(c3)
FOR c2 IN ([10], [20], [30])
) AS pvt
ORDER BY
c1
GO


AMB