Simple Select, not so simple - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Simple Select, not so simple

Author

Topic

looc
Starting Member

10 Posts

Posted - 2009-09-29 : 07:11:14

Ok, i need some help here. Maby it´s impossible but i´ll give it a try..
I have a table with 4 col and a want to make a select and get serialNumber:s in return.

The select is from a search, so i want to search for ex: bios version and operating system and servicepack ....

The problem is that the table is formated in a strange way for this to be easy.
Anyone got a clever idea how to make a search function?


Serail		fieldClass				fieldName		fieldValue
-------------------------------------------------------------------------------------------------------------
VMware-56 	Win32_Bios				BIOSVersion		INTEL  - 6040000
VMware-56 	Win32_Bios				Name			PhoenixBIOS 4.0 Release 6.0
VMware-56 	Win32_Bios				SerialNumber		VMware-56
VMware-56 	Win32_Bios				SMBIOSBIOSVersion	6.00
VMware-56 	Win32_Bios				Version			INTEL  - 6040000
VMware-56 	Win32_OperatingSystem			Caption			Microsoft Windows XP Professional
VMware-56 	Win32_OperatingSystem			CSDVersion		Service Pack 2
VMware-56 	Win32_OperatingSystem			CSName			abc123
VMware-56 	Win32_OperatingSystem			Name			Microsoft Windows XP Professional
VMware-56 	Win32_OperatingSystem			Version			5.1.2600
VMware-56 	Win32_NetworkAdapterConfiguration	Description		VMware Accelerated AMD PCNet Adapter
VMware-56 	Win32_NetworkAdapterConfiguration	DHCPEnabled		True
VMware-56 	Win32_NetworkAdapterConfiguration	DNSHostName		abc123
VMware-56 	Win32_NetworkAdapterConfiguration	IPAddress		192.168.48.131
VMware-56 	Win32_NetworkAdapterConfiguration	MACAddress		00:0C:29:76:C4:71

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 07:15:06

what you need is to pivot on fieldClass field and then search. have a look at syntax of PIVOT in books online

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-29 : 07:20:42

Hi Vishak,
I think Looc's requirement is only to retrieve the serial Number column. he doesnt want the fieldName values to be dispalyed as a column.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-29 : 07:22:58

Maybe Visakh means to pivot into a derived table and then query that derived table...

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 07:24:08

something like


SELECT *
FROM
(
SELECT Serial,
[Win32_Bios] AS Bios,
[Win32_OperatingSystem] AS OperatingSystem,
[Win32_NetworkAdapterConfiguration] AS NetworkAdapterConfiguration 
FROM
(
SELECT DISTINCT Serial,fieldClass,
STUFF((SELECT ',' +fieldName + ':'+ fieldValue 
FROM YourTable
WHERE Serial=t.Serial
AND fieldClass=t.fieldClass
FOR XML PATH('')),1,1,'') AS SearchField
FROM YourTable t
)r
PIVOT (MAX(SearchField) FOR fieldClass IN ([Win32_Bios],[Win32_OperatingSystem],[Win32_NetworkAdapterConfiguration]))p
)m
WHERE Bios LIKE '%,' + @BiosKeyword + ',%'
AND OperatingSystem LIKE '%,' + @OSKeyword + ',%'
....

@BiosKeyword etc you pass as parameter

looc
Starting Member

10 Posts

Posted - 2009-09-29 : 07:39:20

That is realy cool.
Thanx , i think i can make something out of this..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 07:41:04

welcome...let us know how u got on..

looc
Starting Member

10 Posts

Posted - 2009-09-29 : 08:19:40

The function weks fine for searching but to understand i need to break it down and need some pointers.

If i would like just show the bios fields for one serial in a pivot


BIOSVersion	Name						SerialNumber	SMBIOSBIOSVersion	Version
--------------------------------------------------------------------------------------------------------------------
DELL   - 15	Phoenix ROM BIOS PLUS Version 1.10 2.6.0	17VPT2J		2.6.0			DELL   - 15

An the maby show all values for Bios and Operatingsystem

And the maby show some vales form Bios and Operationgsystem

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 08:41:17

do you mean this?


select * from
(
select *
from table 
where fieldClass='Win32_Bios')t
pivot (max(fieldValue) for fieldName IN ([BIOSVersion],[Name],[SerialNumber],[SMBIOSBIOSVersion],[Version]))p

looc
Starting Member

10 Posts

Posted - 2009-09-29 : 08:53:56

No.
If i run this i get.
Msg 265, Level 16, State 1, Line 1
The column name "serialNumber" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 8156, Level 16, State 1, Line 1
The column 'serialNumber' was specified multiple times for 'p'.

If i remove SerialNumber from select i get 5 rows with all col:s and just one value filled in in each col.

quote:

Originally posted by visakh16

do you mean this?


select * from
(
select *
from table 
where fieldClass='Win32_Bios')t
pivot (max(fieldValue) for fieldName IN ([BIOSVersion],[Name],[SerialNumber],[SMBIOSBIOSVersion],[Version]))p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 08:57:19

[code]
select * from
(
select Serial,fieldName,fieldValue
from table
where fieldClass='Win32_Bios')t
pivot (max(fieldValue) for fieldName IN ([BIOSVersion],[Name],[SerialNumber],[SMBIOSBIOSVersion],[Version]))p
[/code]

looc
Starting Member

10 Posts

Posted - 2009-09-29 : 09:10:46

Nice...

Now next q.
Can this be made more dynamic, so i dont have to enter all fields in fieldName and add more fieldClass:es in same pivot?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 13:04:44

yup. but for that you need to use dynamic sql. see below

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

looc
Starting Member

10 Posts

Posted - 2009-09-30 : 03:01:49

I think i got it now, works realy well.
Thanx alot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 01:23:48

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources