Solved

SQL Server TSQL script

Posted on 2015-02-06
11
27 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Hello, As I have seen there a lot of requests regarding monitoring and reporting for exchange 2007 / 2010 / 2013 I have decided to post some thoughts together and link to articles that have helped me. Of course a lot of information you can get…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

773 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