SQL Pivot - Results GROUP BY into View

I have a SQL Pivot query that works, except I need to group the results, create a view of the results, and query the view.  Easy right?

Here is the SQL query:
DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + cast(AttributeName as varchar) + ']',
'[' + cast(AttributeName as varchar)+ ']')
FROM dbo.AppAttValue
WHERE ApplicationName = 'appname'
GROUP BY dbo.AppAttValue.AttributeName

DECLARE @query VARCHAR(8000)

SET @query = 
'SELECT ApplicationName, AppAttValueLink, '+@columns+'
FROM dbo.AppAttValue
PIVOT
(MAX(AppAttValue)
FOR [AttributeName]
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)

Open in new window


I need to GROUP BY the AppAttValueLink, and query the new view.  Any ideas?
frontback45Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
please explain the background to your requirement....

on the face of it it isn't possible to create a view with a dynamic sql element...

however it isn't clear what the actual scenario is....

would a temp table be a valid outcome?
0
frontback45Author Commented:
I need the ability to query the results of the pivot, so any solution that can support queries is acceptable.  I only need the information in this form for certain operations, so a temp table might be a possible solution.  I am trying to find a stable solution to name-value-pair situations.
0
SharathData EngineerCommented:
Post the result of your working query and the expected result
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

frontback45Author Commented:
Sample:

ApplicationName AppAttValueLink FQDN IP Address Operating System

 SymantecBackupExec      e7731382-e765-44af-ba17-fb5988ea9bfe      SERVER2.DOMAIN1.COM      NULL      NULL
SymantecBackupExec      e7731382-e765-44af-ba17-fb5988ea9bfe      NULL      192.168.1.2      NULL
SymantecBackupExec      e7731382-e765-44af-ba17-fb5988ea9bfe      NULL      NULL      MICROSOFT WINDOWS SERVER 2008
SymantecBackupExec      ad257cd6-1de6-49f4-976b-dbe5af8a9f0e       SERVER3.DOMAIN1.COM      NULL      NULL
SymantecBackupExec      ad257cd6-1de6-49f4-976b-dbe5af8a9f0e      NULL      192.168.1.3      NULL
SymantecBackupExec      ad257cd6-1de6-49f4-976b-dbe5af8a9f0e      NULL      NULL      MICROSOFT WINDOWS SERVER 2008

Want:
ApplicationName AppAttValueLink FQDN IP Address Operating System

SymantecBackupExec      e7731382-e765-44af-ba17-fb5988ea9bfe      SERVER2.DOMAIN1.COM      192.168.1.2      MICROSOFT WINDOWS SERVER 2008      
SymantecBackupExec      ad257cd6-1de6-49f4-976b-dbe5af8a9f0e       SERVER3.DOMAIN1.COM      192.168.1.3 MICROSOFT WINDOWS SERVER 2008

It seems that a Group By would work, but I cannot find where to use it in the Pivot.
0
SharathData EngineerCommented:
Can you post the sample data from table AppAttValue?
0
frontback45Author Commented:
ValueID ApplicationName AttributeName AppAttValueLink AppAttValue AccessLevel

1      SymantecBackupExec      FQDN      e7731382-e765-44af-ba17-fb5988ea9bfe      SERVER2.DOMAIN1.COM      True
2      SymantecBackupExec      IP Address      e7731382-e765-44af-ba17-fb5988ea9bfe      192.168.1.2      True
3      SymantecBackupExec      Operating System      e7731382-e765-44af-ba17-fb5988ea9bfe      MICROSOFT WINDOWS SERVER 2008      True
4      SymantecBackupExec      FQDN      ad257cd6-1de6-49f4-976b-dbe5af8a9f0e      
SERVER3.DOMAIN1.COM      True
5      SymantecBackupExec      IP Address      ad257cd6-1de6-49f4-976b-dbe5af8a9f0e      192.168.1.3      True
6      SymantecBackupExec      Operating System      ad257cd6-1de6-49f4-976b-dbe5af8a9f0e      MICROSOFT WINDOWS SERVER 2008      True
7      SymantecBackupExec      FQDN      50c97a07-105b-4ace-bf27-7b0c6a0c2da1      
SERVER4.DOMAIN1.COM      True
8      SymantecBackupExec      IP Address      50c97a07-105b-4ace-bf27-7b0c6a0c2da1      192.168.1.4      True
9      SymantecBackupExec      Operating System      50c97a07-105b-4ace-bf27-7b0c6a0c2da1      MICROSOFT WINDOWS SERVER 2003      True
10      SymantecBackupExec      FQDN      1df221b4-e009-40bd-95f7-21c0420474b3      
SERVER5.DOMAIN1.COM      True
11      SymantecBackupExec      IP Address      1df221b4-e009-40bd-95f7-21c0420474b3      192.168.1.5      True
12      SymantecBackupExec      Operating System      1df221b4-e009-40bd-95f7-21c0420474b3      MICROSOFT WINDOWS SERVER 2003      True
13      SymantecBackupExec      FQDN      8e9519bf-5730-4690-8357-e6b7f9298317      
SERVER6.DOMAIN1.COM      True
14      SymantecBackupExec      IP Address      8e9519bf-5730-4690-8357-e6b7f9298317      192.168.1.6      True
15      SymantecBackupExec      Operating System      8e9519bf-5730-4690-8357-e6b7f9298317      MICROSOFT WINDOWS SERVER 2003      True
16      SymantecBackupExec      FQDN      2b833060-6b81-4e9c-b737-d99bfd998b8f      
SERVER7.DOMAIN1.COM      True
17      SymantecBackupExec      IP Address      2b833060-6b81-4e9c-b737-d99bfd998b8f      192.168.1.7      True
18      SymantecBackupExec      Operating System      2b833060-6b81-4e9c-b737-d99bfd998b8f      MICROSOFT WINDOWS SERVER 2003      True
19      SymantecBackupExec      FQDN      97d4f337-463f-4222-b0b4-367914072120      
SERVER8.DOMAIN1.COM      True
20      SymantecBackupExec      IP Address      97d4f337-463f-4222-b0b4-367914072120      192.168.1.8      True
21      SymantecBackupExec      Operating System      97d4f337-463f-4222-b0b4-367914072120      WINDOWS 7      True
22      SymantecBackupExec      FQDN      9bdc7f97-67d1-442f-aa03-07b7aa19f5b0      
SERVER9.DOMAIN1.COM      True
23      SymantecBackupExec      IP Address      9bdc7f97-67d1-442f-aa03-07b7aa19f5b0      192.168.1.9      True
24      SymantecBackupExec      Operating System      9bdc7f97-67d1-442f-aa03-07b7aa19f5b0      WINDOWS 7      True
25      SymantecBackupExec      FQDN      446d9dca-83f9-4e9d-be73-872909e5e8cc      
SERVER10.DOMAIN1.COM      True
26      SymantecBackupExec      IP Address      446d9dca-83f9-4e9d-be73-872909e5e8cc      192.168.1.10      True
27      SymantecBackupExec      Operating System      446d9dca-83f9-4e9d-be73-872909e5e8cc      WINDOWS 7      True
0
SharathData EngineerCommented:
try like this.
DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + cast(AttributeName as varchar) + ']',
'[' + cast(AttributeName as varchar)+ ']')
FROM dbo.AppAttValue
WHERE ApplicationName = 'appname'
GROUP BY AttributeName
DECLARE @query VARCHAR(8000)

SET @query = 
'SELECT ApplicationName, AppAttValueLink, '+@columns+'
FROM (SELECT ApplicationName,AppAttValueLink, AttributeName,AppAttValue FROM dbo.AppAttValue) t1
PIVOT
(MAX(AppAttValue)
FOR [AttributeName]
IN (' + @columns + ')
)
AS p'
EXECUTE(@query)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frontback45Author Commented:
Just an update...I am going to test the proposed solution tonight, and I'll let you know if it works.  Thanks
0
frontback45Author Commented:
Excellent! Works perfect.  Thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.