Solved

SQL Server TSQL script

Posted on 2015-02-06
11
23 Views
Last Modified: 2016-06-18
I'm working on an MS SQL server TSQL script which seems easier than it is.

Using MS SQL Server 2008 R2 I'm working with a single table which contains
records having a field OBJECT, a field TYPE, and a field VALUE.

There are 8 TYPEs in use and a single VALUE and OBJECT name on each record.
The same OBJECT name may appear on multiple records having different TYPEs,
however there are no such rows which have the same TYPE for the same OBJECT.

I'm trying to obtain rows using SELECT with no more than 1 row per OBJECT
an OBJECT field and fields containing VALUE or blank ('') for 8 different
fields of TYPE.
0
Comment
Question by:VB6chuck
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
Select [Object]
From myTable
pivot ([Value] for [Type] in ([T1], [T2], ... , [T8]) as mypvt
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 500 total points
Comment Utility
That will give you the value or Null.

To get "", once you have the above working, then start with
with myTable as (

And end with

)
select [Object]. IsNull(T1, ""), ..., IsNull(T8, "")
From myTable
0
 
LVL 2

Expert Comment

by:Averitteg
Comment Utility
Can you post an image of your data and/or structure to show what data you're working with?  You say you have 8 types, but how many total records are you dealing with?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
:(  please don't use images of data; re-usable plain text is far better. e.g.

MyTable
OBJECT, TYPE, VALUE
X,A,one
Y,B,two

plus, in addition to "sample data" also provide the "expected result"

If you provide both sample data and expected result you maximize the the possibility of a quick solution (and typically also reduce the number of follow-up questions).
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
The following query is my best guess from your description.

select
          object, type, max(value)
from YourTable
where value is NOT NULL or value = ''
group by
          object, type
having count(*) = 1


I would prefer of course to work with sample data and expected result, this removes guesswork.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I can't think of slicker way off hand, although I'm sure there is one, so for now I'd just use a quick-and-dirty method that still only has to process a reasonable number of rows, I think:


IF OBJECT_ID('tempdb.dbo.#types') IS NOT NULL
    DROP TABLE #types
IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL
    DROP TABLE #objects
CREATE TABLE #types (
    type_num tinyint IDENTITY(1, 1) NOT NULL,
    type varchar(30) NOT NULL,
    min_object varchar(30) NOT NULL
    )
CREATE TABLE #objects (
    object varchar(30) NOT NULL PRIMARY KEY,
    type varchar(30) NOT NULL UNIQUE,
    value varchar(30) NULL
    )
DECLARE @type_count tinyint
DECLARE @type_num tinyint

INSERT INTO #types
SELECT type, MIN(object) AS min_object
FROM table_name
GROUP BY type
ORDER BY type
SET @type_count = @@ROWCOUNT
--SELECT @type_count AS type_count
--SELECT * FROM #types

SET @type_num = 1

WHILE @type_num <= @type_count
BEGIN
    INSERT INTO #objects
    SELECT TOP (1) tn.object, tn.type, tn.value
    FROM table_name tn
    INNER JOIN #types t ON
        tn.type = (SELECT type FROM #types t WHERE t.type_num = @type_num)
    WHERE        
        tn.object >= t.min_object AND
        NOT EXISTS(SELECT 1 FROM #objects o WHERE o.object = tn.object)
    SET @type_num = @type_num + 1
END --WHILE

SELECT o.*
FROM #objects o
ORDER BY o.type
0
 

Author Comment

by:VB6chuck
Comment Utility
PortletPaul-

Re your request for sample data and desired result, that's an excellent idea, here they are

MyTable
147,000 rows containing
ObjectNumber, Type, and Value (Type will contain TypeName, one of Type1 thru Type8)(depending on type, Value may be text or number, and in the result column will appear appropriately)
(ObjectNumber will appear in one or more rows, each having a type, and usually but not always, a value appropriate to the type) ( any particular ObjectNumber will have only 1 row with a particular Type)

wish to receive by SELECT
 ( less rows, at minimum 1/8th as many, if all ObjectNumbers are connected to all 8 possible values, which does not need to ever be the case)
ObjectNumber, Type1Value, Type2Value, Type3Value, Type4Value...Type8Value (where ObjectNumber is unique and values may contain up to 8 separate values)

I've not reviewed all other replies yet.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
LOL

"sample data" is usually provided (e.g. a copy/paste into a code block, or an excel file) and not described.
The purpose of sample data is manyfold, but most frequently it is used to populate tables. I cannot populate a table with a brief description of what the data looks like.

"expected result" also isn't a description. It is a grid of derived data, in the required layout, and based on the sample data.

Together "sample data" and "expected result" form a specification of what you want as a query and you spend less time describing things and answering our questions.

{+edit}
and "sample" does mean just a few records, megabytes of data is not the intention.
0
 

Author Comment

by:VB6chuck
Comment Utility
I am out of town and unable to give the question full attention at this time.  I have not been able to pursue all suggestions made up to now.  I expect to return to this the week of March 2nd and more fully engage with the suggestions. I apologize for the delay.

Thanks,

VB6Chuck
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
ok, no problem
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now