How to Create a Pivot Table Query?

Question

Need some help coming up with a Pivot Table for the following scenario on SQL Server 2008. I don't normally do much more than basic SQL queries and this one has me a bit stumped.

View #1 - v_R_System (Need Name0 column from here) View #2 - v_GS_ADD_REMOVE_PROGRAMS (need DisplayName0 from here)

The non-pivot query looks like this:

SELECT DISTINCT sys.Name0 AS [SYSTEM NAME], arp_x86.DisplayName0 AS [ADD/REMOVE PROGRAMS]
FROM         v_R_System AS sys LEFT OUTER JOIN
                      v_GS_ADD_REMOVE_PROGRAMS AS arp_x86 ON sys.ResourceID = arp_x86.ResourceID
WHERE     (sys.Name0 LIKE 'SRV%')
GROUP BY arp_x86.DisplayName0, sys.Name0

The results from the query above simply displays two columns, one for System Name and the other for Add/Remove Programs data.

System Name     Add/Remove Programs
------------------------------------
SRV01           APP01
SRV01           APP02
SRV01           APP03
SRV02           APP01
SRV02           APP03

The problem is that we get multiple rows for each server so what we want instead is to have 1 row for each server but have the list of software displayed across the top and then put an "*" in the software column if it is installed on a particular server.

System Name     APP01     APP02     APP03
------------------------------------------
SRV01             *         *         *
SRV02             *                   *

Any assistance is greatly appreciated!

Thanks.

Is it mysql or sql-server? pick one and take the other tag out please. — Jorge Campos, Nov 27 '13 at 21:37
[PIVOT](http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx) can be used a shown here as long as you know the names of the columns that will be formed after applying it. — user2989408, Nov 27 '13 at 21:41

score 1 · Answer 1 · edited May 23 '17 at 10:31

1

If you have just theese tree apps this should do:

select
       sys.Name0,
       max(case arp_x86.DisplayName0 
                 when 'APP01' then arp_x86.DisplayName0 else '' end) as APP01,
       max(case arp_x86.DisplayName0 
                 when 'APP02' then arp_x86.DisplayName0 else '' end) as APP02,
       max(case arp_x86.DisplayName0 
                 when 'APP03' then arp_x86.DisplayName0 else '' end) as APP03
  from v_R_System AS sys 
           LEFT OUTER JOIN v_GS_ADD_REMOVE_PROGRAMS AS arp_x86 
                   ON sys.ResourceID = arp_x86.ResourceID
where sys.Name0 LIKE 'SRV%'
group by sys.Name0

This is just a sql way to do it.

For a Dynamic PIVOT table you can see this answer, you will have to adapt to fit your problem.

edited May 23 '17 at 10:31

Community

1
1

answered Nov 27 '13 at 21:46

Jorge Campos

22,647
7
56
87

I just listed those as an example, each server could have 30 apps or more. – user1580685 Nov 27 '13 at 22:31
Then a procedure is a way to go for you! – Jorge Campos Nov 28 '13 at 02:54
But how does it work if the list of apps are not known? Some servers may have 30, others 35, others 28, etc. It seems this method would only work if I list each individual app as a known app. The solution needs to be dynamic. – user1580685 Nov 28 '13 at 14:38

How to Create a Pivot Table Query?

1 Answers1