rightontarget (9/25/2013)

---

My bad, I messed up.

Here is what I need to run, but the output is truncated:

drop table A;

CREATE TABLE A(

[col_1] [nvarchar](30) NOT NULL,

[col_2] [varchar](256) NULL,

[col_3] [varchar](max) NULL,

[col_4] [varchar](max)...

In SSMS you can right-click on the job, select "Script Job as" > "Create to" > ("Query Window" or "File"). This will produce the DDL to create each job. I...

FYI, the sample ddl you provided had an error

I think insert into A (col_1, col_2, col_3)

should really be insert into A (col_1, col_2, col_3, col4)

All that said, No truncation, simple...

This is the way that I would do it.

--Your Data

WITH your_table AS

(SELECT * FROM

(VALUES

(12402223,171906,'Quality And Reliability Engineering',1,4),

(12402223,171906,'Quality And Reliability Engineering',2,10),

(12402223,171906,'Quality And Reliability Engineering',3,11),

(12402223,171906,'Quality And Reliability Engineering',4,5),

(12402223,171906,'Quality And...

OPTION 1

The down and dirty way (not recommended) would be to set the column visibility based on an expression like so:

=User!UserID <> "Manager #1" AND User!UserID <> "Manager #2"

OPTION 2

1)...

Am I going to be reduced to coding up something to list each column, its datatype, FK relationships, etc, then running it against the current and previous DB and eyeballing...

If not for the un-closed <OrderID> tag you would have a well-formed fragment that you could query via XPath. What Scott and Sean included will be better for your requirement....

I would add that it is good that you are on SQL Server Central. Use this site; it's a great resource! Ask questions, read through the forums and the articles....

Well done Magoo; I knew there was a much better way but drew a blank.

Jeff Moden (9/16/2013)

---

Just to backup Maggo's solution... the method he used is called a "CROSSTAB"....

This could still be optimized but will perform a little better than my previous query:

--OUTPUT

SELECT e.MSKEY AS NUMBER,

ISNULL(fn.AVALUE,'') AS FNAME,

ISNULL(mn.AVALUE,'') AS MNAME,

ISNULL(ln.AVALUE,'') AS LNAME,

ISNULL(em.AVALUE,'') AS EMAIL

FROM Entries e

LEFT JOIN Entries...

Below is some DDL and a solution:

--DDL

USE tempdb

IF OBJECT_ID('tempdb..Entries') IS NOT NULL DROP TABLE Entries;

CREATE TABLE Entries (MSKEY int, ATTRNAME varchar(20), AVALUE varchar(20), primary key (MSKEY, ATTRNAME));

INSERT Entries (MSKEY,...

If I understand the original OP correctly (" If something has been credited,...

Using the sample code above, I believe this will do the trick:

WITH billed_credited AS

(

SELECT CaseNum, MyHours, COUNT(MyHours) AS bc

FROM #SomeTable

GROUP by CaseNum, MyHours

)

SELECT SUM(x.Myhours) TotalHours

FROM #sometable st

CROSS APPLY...

This is hard without DDL but assuming you cannot have this:

Case | Hours | Type

xxx | 3 | 1

xxx | 3 | 1

Or this:

Case | Hours | Type

xxx | 3...

You should still be able to use UNION ALL in that scenario. Below is some code to create sample data similar to what I think you are dealing with. ...