CrossTab ''like'' Query
-
Hello
This is going to be a bit long wided, so I apologise in advance.
I have a db with among others the following 3 tables:
/****** Object: Table [dbo].[CIAttribute_T] Script Date: 08/12/2005 16:12:31 ******/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CIAttribute_T]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [CIAttribute_T] (
[ConfigurationItemID] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
[AttributeValue] [varchar] (1000) ) ON [PRIMARY]
END
GO
/****** Object: Table [dbo].[Attribute_T] Script Date: 08/12/2005 16:09:46 ******/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Attribute_T]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Attribute_T] (
[AttributeID] [int] IDENTITY (1, 1) NOT NULL ,
[AttributeName] [varchar] (50) ,
[AttributeDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AttributeTypeID] [int] NOT NULL ,
[AttributeLength] [int] NOT NULL) ON [PRIMARY]
END
GO
/****** Object: Table [dbo].[ConfigurationItem_T] Script Date: 08/12/2005 16:10:44 ******/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ConfigurationItem_T]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [ConfigurationItem_T] (
[ConfigurationItemID] [int] IDENTITY (1, 1) NOT NULL ,
[CITypeID] [int] NOT NULL ,
[CIName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
END
GO
I designed this so as to allow a user to define their own attributes for a given type of entity, Configuration Item in this example.
I have the following query:
select ci.ciname, a.attributeName, attributevalue from ciAttribute_t cia
inner join configurationItem_T ci on cia.configurationitemid = ci.configurationitemid
inner join attribute_T a on cia.attributeId = a.attributeId
which returns a result set similar to:
ciName AttributeName AttributeValue
--------------- ------------------------------- ----------------------------------
ALADDIN Model Virtual
ALADDIN RAM 368 MB
ALADDIN CPU Intel(R) Xeon(TM) CPU 2.80GHz
APPLE Hard Drive Make/Model NECVMWar VMware IDE CDR10
APPLE HardDriveCapacity 100.00 GB
APPLE RAM 368 MB
APPLE IP Address 192.168.35.206
APPLE CPU Intel(R) Xeon(TM) CPU 2.80GHz
ASH Hard Drive Make/Model NECVMWar VMware IDE CDR10
ASH HardDriveCapacity 19.99 GB
ASH IP Address 192.168.35.112
BEACH Hard Drive Make/Model NECVMWar VMware IDE CDR30
BEACH RAM 368 MB
BEACH HardDriveCapacity 10.00 GB
BEACH IP Address 192.168.35.114
KIRK Hard Drive Make/Model LG CD-ROM CRN-8245B
KIRK IP Address 10.21.31.128
KIRK RAM 368 MB
etc.
What I want to do is a some type of crosstab type query to return:
ciName Model RAM IP Address etc...
ALADDIN Virtual 368 Mb NULL
APPLE Virtual 368 Mb 192.168.35.206
ASH NULL NULL 192.168.35.112
BEACH IBM 345 368 Mb 192.168.35.114
KIRK NULL 368 Mb 10.21.31.128
What I don't want to do is aggregate any values, just group them.
Anyone out there seen this? Or should I tear up my design & re-think how to store unknown 'sets' together?
Dave Jackson
-
http://www.itrain.de/knowhow/sql/tsql/pivot/sp_transform_v1_1.asp - construct a sp that pivots the columns as rows and stuffs data in it. I dont think you are looking for an sp as a solution but it might help.
-
Thanks for the Uri, but it still uses an aggregate
I have seen similar approaches, and had a go at adapting them but end up with a result set like:
ciName Model RAM IP Address etc...
ALADDIN Virtual 0 0
ALADDIN 0 368 Mb 0
ALADDIN 0 0 NULL
APPLE Virtual 0 0
APPLE 0 368 Mb 0
APPLE 0 0 192.168.35.206
etc...
I can get this far but then want to loop through (?) keeping just attributes that are populated. I can't think how though
Dave J
-
Does it help if we can generate columns in a temp table based on values in Attribute_name and insert values in the temp table.
Create Table #temp1(
column1 int
)
Declare @col2 as varchar(10)
set @col2 = 'Column2'
select @col2
exec ('alter Table #temp1 add '+ @col2 +' int')
Just another Idea..!!
-
Good thinking Chandra. I'll have to try it that way sometime because it looks like a really good idea especially since it wouldn't have the 8k limit in the method I'm about to show. Still, you'd have to get the data in after the temp table was constructed.
David,
This is "self healing" in that if a user adds and AttributeName, it will be added to the output, "automagically"...
--===== If the temp table to hold the results in exists, drop it
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE Results'
--===== Populate the temp table with results from your original query
SELECT ci.ciName, a.AttributeName, AttributeValue
INTO #Results
FROM ciAttribute_t cia
INNER JOIN configurationItem_T ci
ON cia.configurationitemid = ci.configurationitemid
INNER JOIN attribute_T a
ON cia.attributeId = a.attributeId
--===== Declare some local variables to hold some Dynamic SQL
DECLARE @MySQL1 VARCHAR(8000)
DECLARE @MySQL2 VARCHAR(8000)
DECLARE @MySQL3 VARCHAR(8000)
--===== Build the SELECT clause
SET @MySQL1 = 'SELECT ciName,'+CHAR(13)
--===== Build the select LIST (do not try to reformat or you'll mess it up!)
SELECT @MySQL2 = ISNULL(@MySQL2+',
','')
+'MIN(CASE WHEN AttributeName = ''' + AttributeName + ''' THEN AttributeValue ELSE NULL END) AS '+'['+AttributeName+']'
FROM (SELECT DISTINCT AttributeName AS AttributeName FROM #Result)d
--===== Build the FROM and GROUP BY clauses
SELECT @MySQL3 = CHAR(13)+'FROM #Result GROUP BY ciName'
--===== Display the resulting SQL (you can take this piece out, just for demo)
PRINT @MySQL1+@MySQL2+@MySQL3
--===== Execute the Dynamic SQL
EXEC (@MySQL1+@MySQL2+@MySQL3)
Here's the Dynamic SQL that would get executed using the data you posted...
SELECT ciName,
MIN(CASE WHEN AttributeName = 'CPU' THEN AttributeValue ELSE NULL END) AS [CPU],
MIN(CASE WHEN AttributeName = 'Hard Drive Make/Model' THEN AttributeValue ELSE NULL END) AS [Hard Drive Make/Model],
MIN(CASE WHEN AttributeName = 'HardDriveCapacity' THEN AttributeValue ELSE NULL END) AS [HardDriveCapacity],
MIN(CASE WHEN AttributeName = 'IP Address' THEN AttributeValue ELSE NULL END) AS 127.0.0.1,
MIN(CASE WHEN AttributeName = 'Model' THEN AttributeValue ELSE NULL END) AS [Model],
MIN(CASE WHEN AttributeName = 'RAM' THEN AttributeValue ELSE NULL END) AS [RAM]
FROM #Result GROUP BY ciName
The result set from the Dynamic SQL execution comes out a bit too wide to post here but it'll be what you want.... and don't pishaw the aggragate MIN until you've tried this... it's essential to making it work.
______________________________________________________________________________________________________________________________________________
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
I am not worthy!
If you are ever in Manchester UK, I'll buy you a pint.
Cheers.
Dave J
-
Had a similar thing at one stage...
What about
select ciName, (select attributeValue from ciAttribute_t cia where cia.configurationItemID = ci.configurationItemID and attributeID = [ATTRIBUTE_ID_FOR_MODEL - OR DO JOIN TO ATTRIBUTES TABLE] ) as Model, (select attributeValue from ciAttribute_t cia where cia.configurationItemID = ci.configurationItemID and attributeID = [ATTRIBUTE_ID_FOR_RAM - OR DO JOIN TO ATTRIBUTES TABLE] ) as RAM, (ETC) from configurationItem_T ci
That has no min/max/grouping/etc. You should try it a variety of ways and check out the execution plan that SQL Server generates - ensure you have appropriate indices.. Probably need them on configurationItemID in both tables and the attributeID columns in both tables - the configutationItemID + attributeID columns in their shared table should be in the one index (probably clustered unless you have a good reason to put it on the value field).
I like the solution that automagically adds the fields, but if you don't wish to use dynamic SQL and the possible pitfalls that arise with it, this solution might be your best bet... But, if you are happy with Dynamic SQL for this solution (and I'm not saying it's wrong), then you may wish to use it instead... Just depends on how fast SQL executes the code, etc, etc.
Good luck!!
-
Ian,
your solution is OK if you know what the attribute names are in advance. My problem is I don't.
So, I don't see an alternative to using Dynamic SQL, although I will build Jeffs approach into a SP that takes a CITypeID, which will restrict it somewhat.
Thanks for the input.
Dave.
-
Here is the sp, with typos taken out. Jeff hadn't tested his code, just typed it straight out. Which only increases my respect for the man.
CREATE Procedure usp_CMDBCrossTab@CIITemType int,
@debug bit = 0
As
/*
Example: usp_CMDBCrossTab 1, 1 --optional @debug flag prints the query too.
*/
Set NOCount On
---===== If the temp table to hold the results in exists, drop it
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
---===== Populate the temp table with results from your original query
SELECT ci.ciName,
a.AttributeName,
AttributeValue
INTO #Results
FROM ciAttribute_t cia
INNER JOIN configurationItem_T ci
ON cia.configurationitemid = ci.configurationitemid
INNER JOIN attribute_T a
ON cia.attributeId = a.attributeId
WHERE ci.CITypeId = @CIITemType
IF @@RowCount = 0
Return
CREATE
INDEX [test] ON #Results ([AttributeName])
--===== Declare some local variables to hold some Dynamic SQL
DECLARE
@MySQL1 VARCHAR(8000)
DECLARE
@MySQL2 VARCHAR(8000)
DECLARE
@MySQL3 VARCHAR(8000)
--===== Build the SELECT clause
SET @MySQL1 = 'SELECT ciName,'
--===== Build the select LIST (do not try to reformat or you'll mess it up!)
SELECT @MySQL2 = ISNULL(@MySQL2 + ',','') + '
MIN(CASE WHEN AttributeName = ''' + AttributeName + '''
THEN AttributeValue ELSE NULL END) AS ' + '[' + AttributeName + ']'
FROM (SELECT DISTINCT TOP 100 PERCENT AttributeName AS AttributeName
FROM #Results
Order by AttributeName) d
--===== Build the FROM and GROUP BY clauses
SELECT @MySQL3 = CHAR(13) + 'FROM #Results GROUP BY ciName'
--===== Display the resulting SQL (you can take this piece out, just for demo)
if @Debug = 1
PRINT @MySQL1+@MySQL2+@MySQL3
--===== Execute the Dynamic SQL
EXEC ( @MySQL1 +@MySQL2 +@MySQL3 )
-
Just keep in mind, David... it breaks at 8k... I think that won't happen for quite some time, though.
Manchester UK, eh? I've always wanted to see that place... I may have to fly out sometime.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
No probs
Glad you found a solution -
Thanks for the nice things you said, David. Surprisingly enough, I don't believe you'll find a huge performance gap just because of the use of the Dynamic SQL. Dynamic SQL does provide a drain on performance when used with RBAR (Row By Agonizing Row) code but not much with set-based code. If it does recompile, it'll only be once for the run and that might happen with non-Dynamic SQL anyway if the data in the tables has changed enough.
Anyway, thanks again and I'm very pleased that you took the time to post your final solution. That, in itself, is a great reward for me...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's only common courtesy to post the solutions, especially when the issue is a generic one.
I always try to post them as:
- I think it may be of help to others.
- I know where I can find it again.
If you do make it to Manchester, bring an umbrella.
Dave J
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply