April 28, 2017 at 9:03 am
I'm feeling remarkably clueless today because I can't figure out what is wrong with the following query. Any help is appreciated.
I've got 18 rows for each RecordID. The LocalName is the column I'm pivoting, i.e. where the contents become the column headers. The Content field is the value.
I'm expecting to get one row for each RecordID with the Content for each LocalName in the appropriate column.
What I'm getting is 18 rows for each RecordID although the content is in the proper column. It's not rolling up to the RecordID.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols = ISNULL(@cols + ',','') + QUOTENAME(LocalName)
FROM (SELECT DISTINCT localName FROM [RES-DW].[dbo].vwAgentInfo) as pc
SET @query = 'SELECT recordId,' + @cols +
'FROM [RES-DW].[dbo].vwAgentInfo a
PIVOT
(min(content)
FOR localName IN (' + @cols + ')) pvt order by recordid'
print @query
EXECUTE sp_executesql @query;
The code dynamically generated and executed looks like this. I guess I don't understand why I'm not getting the results I'm expecting as this looks right to me.
SELECT recordId,[Adapter],[bit],[firstdispatcher],[FQDN],[info],127.0.0.1,[IPAddress],[IPGateway],[IPGateways],[IPSubnet],[LAN],[MAC],[OS],[ossuite],[ostype],[procarch],[SP],[systeminfo]FROM [RES-DW].[dbo].vwAgentInfo a
PIVOT
(min(content)
FOR localName IN ([Adapter],[bit],[firstdispatcher],[FQDN],[info],127.0.0.1,[IPAddress],[IPGateway],[IPGateways],[IPSubnet],[LAN],[MAC],[OS],[ossuite],[ostype],[procarch],[SP],[systeminfo])) pvt order by recordid
"Beliefs" get in the way of learning.
April 28, 2017 at 9:22 am
Robert,
I think your issue is with "min(content)". I don't see content in your list of fields. You may want to substitute an actual column name.
Regards,
Matt
April 28, 2017 at 9:40 am
Content is the column name and the value is being returned but what is getting returned looks something like this instead of one row with the appropriate value in the appropriate column.:
RecordID Adapter Bit FirstDispatcher FQDN info IP ......
1 NULL NULL WS2012 NULL NULL NULL
1 NULL 64 NULL NULL NULL NULL
1 NULL NULL NULL F.Q.D.N NULL NULL
1 NULL NULL NULL NULL stuff NULL
1 NULL NULL NULL NULL NULL 10.1.100.28
"Beliefs" get in the way of learning.
April 28, 2017 at 9:42 am
Take a look at how I took your query and just supplied a temp table name and all the possible values with some sample numbers to work with. If you have some sample data you can post in a consumable form (meaning table create and insert statements that actually work), we might be able to get a better insight into what you're trying to do. I got a single row result, so I'm not sure where the grief might be.CREATE TABLE #vwAgentInfo (
recordId int NOT NULL,
localName varchar(30),
content int
);
INSERT INTO #vwAgentInfo (recordId, localName, content)
SELECT recordId, localName, content
FROM (
VALUES (1, 'Adapter', 2),
(1, 'bit', 4),
(1, 'firstdispatcher', 6),
(1, 'FQDN', 8),
(1, 'info', 2),
(1, 'IP', 4),
(1, 'IPAddress', 6),
(1, 'IPGateway', 8),
(1, 'IPGateways', 2),
(1, 'IPSubnet', 4),
(1, 'LAN', 6),
(1, 'MAC', 8),
(1, 'OS', 2),
(1, 'ossuite', 4),
(1, 'ostype', 6),
(1, 'procarch', 8),
(1, 'SP', 2),
(1, 'systeminfo', 4)
) AS X (recordId, localName, content);
SELECT recordId, [Adapter],[bit],[firstdispatcher],[FQDN],[info],127.0.0.1,[IPAddress],[IPGateway],[IPGateways],[IPSubnet],[LAN],[MAC],[OS],[ossuite],[ostype],[procarch],[SP],[systeminfo]
FROM #vwAgentInfo
PIVOT (MIN(content) FOR localName IN ([Adapter], [bit], [firstdispatcher], [FQDN], [info], 127.0.0.1, [IPAddress], [IPGateway], [IPGateways],
[IPSubnet], [LAN], [MAC], [OS], [ossuite], [ostype], [procarch], [SP], [systeminfo])
) AS pvt
ORDER BY recordId;
DROP TABLE #vwAgentInfo;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 28, 2017 at 9:57 am
Is it possible that RecordID is not the correct field to group on?
If I take Steve's sample and tweak it slightly (with a different RecordID for each row), I get the output that you describe.
CREATE TABLE #vwAgentInfo (
recordId int NOT NULL,
localName varchar(30),
content int
);
INSERT INTO #vwAgentInfo (recordId, localName, content)
SELECT recordId, localName, content
FROM (
VALUES (1, 'Adapter', 2),
(2, 'bit', 4),
(3, 'firstdispatcher', 6),
(4, 'FQDN', 8),
(5, 'info', 2),
(6, 'IP', 4),
(7, 'IPAddress', 6),
(8, 'IPGateway', 8),
(9, 'IPGateways', 2),
(10, 'IPSubnet', 4),
(11, 'LAN', 6),
(12, 'MAC', 8),
(13, 'OS', 2),
(14, 'ossuite', 4),
(15, 'ostype', 6),
(16, 'procarch', 8),
(17, 'SP', 2),
(18, 'systeminfo', 4)
) AS X (recordId, localName, content);
SELECT recordId, [Adapter],[bit],[firstdispatcher],[FQDN],[info],127.0.0.1,[IPAddress],[IPGateway],[IPGateways],[IPSubnet],[LAN],[MAC],[OS],[ossuite],[ostype],[procarch],[SP],[systeminfo]
FROM #vwAgentInfo
PIVOT (MIN(content) FOR localName IN ([Adapter], [bit], [firstdispatcher], [FQDN], [info], 127.0.0.1, [IPAddress], [IPGateway], [IPGateways],
[IPSubnet], [LAN], [MAC], [OS], [ossuite], [ostype], [procarch], [SP], [systeminfo])
) AS pvt
ORDER BY recordId;
DROP TABLE #vwAgentInfo;
April 28, 2017 at 10:19 am
Thanks for the help everyone. Ultimately, this is what worked.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols = ISNULL(@cols + ',','') + QUOTENAME(LocalName)
FROM (SELECT DISTINCT localname FROM [RES-DW].[dbo].vwAgentInfo) as pc
SET @query = 'SELECT recordId,' + @cols +
'FROM (SELECT recordId, localname, content FROM [RES-DW].[dbo].vwAgentInfo) a
PIVOT
(min(content)
FOR localName IN (' + @cols + ')) pvt order by recordid'
print @query
EXECUTE sp_executesql @query;'
"Beliefs" get in the way of learning.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply