Solved

SQL Server TSQL script

Posted on 2015-02-06
11
31 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
ID: 40594675
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
ID: 40594682
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
ID: 40594683
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40596535
:(  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
ID: 40596536
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40598912
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
ID: 40599120
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
ID: 40599586
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
ID: 40632083
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
ID: 40632094
ok, no problem
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 45
Help with a script Updating records from other records in the Same table 13 55
Are triggers slow? 7 22
SQL Server 2008 R2 service pack updates 5 42
If, like me, you have a lot of Dell servers in the estate you manage this article should save you a little time. When attempting to login to iDrac on any server I would be presented with two errors. The first reads "Do you want to run this applicati…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

679 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