Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server TSQL script

Posted on 2015-02-06
11
28 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Server Modify insert/update update to use MERGE 13 53
migrate a SQL 2008 to 2016, 2 33
How to search for strings inside db views 4 36
tempdb log keep growing 7 33
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 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