July 19, 2011 at 12:06 pm
I need to take data in the following format (in SQL 2005):
System_NameManufacturerModelTypeOSFunction
XXX-G-BCKM-001HP CompanyDL385 ServerWin2003BCKMS
XXX-G-CLSA-001HP CompanyDL385ServerWin2003CLSA
XXX-G-CLSA-002HP CompanyDL385ServerWin2003CLSA
XXX-G-CLSA-003HP CompanyDL385ServerWin2003CLSA
...
and, filtering on the Function field, turn it into the following:
Attribute XXX-G-CLSA-001XXX-G-CLSA-002XXX-G-CLSA-003
ManufacturerHP CompanyHP CompanyHP Company
Model DL385 DL385 DL385
Type Server Server Server
OS Win2003 Win2003 Win2003
I think that it's possible to do this using dynamic SQL and the PIVOT command, but despite all of my efforts (I've spent a couple of days on it so far), it feels like all I'm doing is banging my head against a wall. Any help would be greatly appreciated...
Thanks.
July 19, 2011 at 12:26 pm
jmoldover (7/19/2011)
I need to take data in the following format (in SQL 2005):
System_NameManufacturerModelTypeOSFunctionXXX-G-BCKM-001HP CompanyDL385 ServerWin2003BCKMS
XXX-G-CLSA-001HP CompanyDL385ServerWin2003CLSA
XXX-G-CLSA-002HP CompanyDL385ServerWin2003CLSA
XXX-G-CLSA-003HP CompanyDL385ServerWin2003CLSA
...
and, filtering on the Function field, turn it into the following:
Attribute XXX-G-CLSA-001XXX-G-CLSA-002XXX-G-CLSA-003ManufacturerHP CompanyHP CompanyHP Company
Model DL385 DL385 DL385
Type Server Server Server
OS Win2003 Win2003 Win2003
I think that it's possible to do this using dynamic SQL and the PIVOT command, but despite all of my efforts (I've spent a couple of days on it so far), it feels like all I'm doing is banging my head against a wall. Any help would be greatly appreciated...
Thanks.
Do you have a set list of System_Names, or will they vary over time?
If they are set, see this article: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
If they will vary, see this article: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
If you are still stuck or have questions I will be happy to assist you, I only ask that you provide a few things to help get started so I can build your test environment on my side.
Please provide:
1. your CREATE TABLE statement
2. some INSERT INTO statements to generate the sample data in your post
3. the query or queries you have tried so far (most important!)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 12:55 pm
There are (at the moment) over 200 different System_Name values, with the strong likelihood of more being added (and some existing ones removed) over time, so the dynamic route is definitely the one to go with. In addition, the number of systems that match a given function code varies from 1 to 12, depending on the code.
Code to generate the sample table and data for this puzzle is as follows (I've left out some of the fields in the actual table since they don't come into play with this):
CREATE TABLE [dbo].[ABL_Hardware](
[RecID] [int] IDENTITY(1,1) NOT NULL,
[System_Name] [varchar](25) NOT NULL,
[System_Function] [varchar](10) NOT NULL,
[System_Type] [varchar](10) NOT NULL,
[Manufacturer] [varchar](50) NULL,
[Product_Name] [varchar](50) NULL,
[Model] [varchar](50) NOT NULL,
[OS_Type] [varchar](10) NULL,
[Notes] [varchar](max) NULL
)
INSERT INTO [dbo].[ABL_Hardware]
([System_Name],[System_Function],[System_Type],[Manufacturer],[Product_Name],[Model],[OS_Type],[Notes])
VALUES
('XXX-G-BCKMS-001', 'BCKMS', 'SERVE', 'Hewlett-Packard Company', 'ProLiant Server', 'DL385 ', 'WIN03', NULL)
INSERT INTO [dbo].[ABL_Hardware]
([System_Name],[System_Function],[System_Type],[Manufacturer],[Product_Name],[Model],[OS_Type],[Notes])
VALUES
('XXX-G-CLSA-001', 'CLSA', 'SERVE', 'Hewlett-Packard Company', 'ProLiant Server', 'DL385', 'WIN03', NULL)
INSERT INTO [dbo].[ABL_Hardware]
([System_Name],[System_Function],[System_Type],[Manufacturer],[Product_Name],[Model],[OS_Type],[Notes])
VALUES
('XXX-G-CLSA-002', 'CLSA', 'SERVE', 'Hewlett-Packard Company', 'ProLiant Server', 'DL385', 'WIN03', NULL)
INSERT INTO [dbo].[ABL_Hardware]
([System_Name],[System_Function],[System_Type],[Manufacturer],[Product_Name],[Model],[OS_Type],[Notes])
VALUES
('XXX-G-CLSA-003', 'CLSA', 'SERVE', 'Hewlett-Packard Company', 'ProLiant Server', 'DL385', 'WIN03', NULL)
INSERT INTO [dbo].[ABL_Hardware]
([System_Name],[System_Function],[System_Type],[Manufacturer],[Product_Name],[Model],[OS_Type],[Notes])
VALUES
('XXX-G-DCAFG-001', 'DCAFG', 'SERVE', 'Hewlett-Packard Company', 'ProLiant Server', 'DL385', 'WIN03', NULL)
INSERT INTO [dbo].[ABL_Hardware]
([System_Name],[System_Function],[System_Type],[Manufacturer],[Product_Name],[Model],[OS_Type],[Notes])
VALUES
('XXX-G-DCAFN-001', 'DCAFN', 'SERVE', 'Hewlett-Packard Company', 'ProLiant Server', 'DL385', 'WIN03', NULL)
INSERT INTO [dbo].[ABL_Hardware]
([System_Name],[System_Function],[System_Type],[Manufacturer],[Product_Name],[Model],[OS_Type],[Notes])
VALUES
('XXX-G-DCRT-001', 'DCRT', 'SERVE', 'Hewlett-Packard Company', 'ProLiant Server', 'DL385', 'WIN03', NULL)
INSERT INTO [dbo].[ABL_Hardware]
([System_Name],[System_Function],[System_Type],[Manufacturer],[Product_Name],[Model],[OS_Type],[Notes])
VALUES
('XXX-G-DNSA-001', 'DNSA', 'SERVE', 'Sun Microsystems Inc.', 'SunFire V240', 'V240', 'SOLAR', NULL)
INSERT INTO [dbo].[ABL_Hardware]
([System_Name],[System_Function],[System_Type],[Manufacturer],[Product_Name],[Model],[OS_Type],[Notes])
VALUES
('XXX-G-DNSC-001', 'DNSC', 'SERVE', 'Sun Microsystems Inc.', 'SunFire V240', 'V240', 'SOLAR', NULL)
INSERT INTO [dbo].[ABL_Hardware]
([System_Name],[System_Function],[System_Type],[Manufacturer],[Product_Name],[Model],[OS_Type],[Notes])
VALUES
('XXX-G-DNSC-002', 'DNSC', 'SERVE', 'Sun Microsystems Inc.', 'SunFire V240', 'V240', 'SOLAR', NULL)
Note that in the data I've provided there are some 5-letter codes that get replaced by longer names via a lookup table cross reference in the actual query (which may make a difference in solving the problem, but I don't think so).
I've tried a couple of dynamic SQL crosstab generators (sp's), but either I'm overlooking something or my data doesn't lend itself to the format they require. Unfortunately, although I've been a DBA for about 10 years now, I haven't had to do anything with (SQL-coded) pivot tables before - I've always used the reporting system (SSRS/Crystal/etc.) to do the processing. In this particular case, however, that's not a viable option at this point (unless there's a way to use an SSRS report from within a MS-Access project front end?).
July 19, 2011 at 1:00 pm
Excellent! The problem domain is set. Have a go at this using the techniques outlined in the article. It has everything you need to arrive at a solution to this puzzle. If you get stuck along the way, ask a specific question about it and show us what you have so far and we'll work through it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply