Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 38
  • Last Modified:

SQL Server TSQL script

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
VB6chuck
Asked:
VB6chuck
  • 4
  • 2
  • 2
  • +2
2 Solutions
 
Phillip BurtonCommented:
Select [Object]
From myTable
pivot ([Value] for [Type] in ([T1], [T2], ... , [T8]) as mypvt
0
 
Phillip BurtonCommented:
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
 
AverittegCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
PortletPaulCommented:
:(  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
 
PortletPaulCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
VB6chuckAuthor Commented:
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
 
PortletPaulCommented:
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
 
VB6chuckAuthor Commented:
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
 
PortletPaulCommented:
ok, no problem
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now