x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

To the chagrin of Jeff or How to format data in T-SQL

EDIT:  Seems to be a lot of formatting question.  Thought I'd turn this into a collection of formatting tips.  I'll list an “Index“ here which will reference the code and “solutions“

1. 10/10/2005 The thread that started this one,  Basically How to summarize data and prevent “duplicate“ entries from appearing in the list

2. 10/11/2005 The very next day we got a question on basically how to create mailing labels

______________________

1. Jeff Smith rightly points out in this thread that the poster is actually asking presentation layer issues.  Now that may very well be true, but I've seen some developers take a very long time, and not get the correct in the presentation layer.  As a matter of course, the more I can “dumb down“ what the developer has to do, for me, the better.

So the question was asked how do I take this Data

Imran 2005-08-01 5000
Imran 2005-09-01 5000
Imran 2005-10-01 7000
Imran NULL         17000
Raja 2005-08-01 5000
Raja 2005-09-01 7000
Raja 2005-10-01 7000
Raja NULL         19000
NULL NULL         36000

And Produce this:

name            date       Amount     
--------------- ---------- -----------
Imran           01/08/2005 5000
                01/09/2005 5000
                01/10/2005 7000
                Sub Total  17000
Raja            01/08/2005 5000
                01/09/2005 7000
                01/10/2005 7000
                Sub Total  19000
Grand Total                36000

I assumed that the data would be in a more raw state when I built this (Note the interesting little twist with the date conversion.  Don't know why I had to use 103, and then use 101 to get it back.  Bizzare):

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([name] varchar(15), [date] datetime, Amount int)
GO

INSERT INTO myTable99([name], [date], Amount)
SELECT 'Imran', '2005-08-01', 5000 UNION ALL
SELECT 'Imran', '2005-09-01', 5000 UNION ALL
SELECT 'Imran', '2005-10-01', 7000 UNION ALL
SELECT 'Raja', '2005-08-01', 5000 UNION ALL
SELECT 'Raja', '2005-09-01', 7000 UNION ALL
SELECT 'Raja', '2005-10-01', 7000
GO

CREATE TABLE myTable00(RowId int IDENTITY(1,1), [name] varchar(15), [date] varchar(10), Amount int)
GO


INSERT INTO MyTable00([name], [date], Amount)
SELECT COALESCE([name], 'Grand Total') AS [name]
 , CASE
  WHEN [date] IS NULL AND [name] IS NULL THEN ''
  WHEN [date] IS NULL THEN 'Sub Total'
         ELSE [date]
   END AS [date]
 , SUM_Amount
  FROM (
 SELECT [name], CONVERT(varchar(10),[date],103) AS [date], SUM(Amount) AS SUM_Amount
   FROM myTable99
 GROUP BY  [name], [date]
 WITH ROLLUP
 ) AS xxx
GO

   SELECT CASE WHEN a.[name] LIKE 'Grand%' THEN a.name
        ELSE COALESCE(xxx.[name],'')
   END AS [name]
   , a.[date]
   , a.Amount
     FROM myTable00 a
LEFT JOIN (
     SELECT [name], CONVERT(varchar(10),MIN(CONVERT(datetime,[date])),101) AS [date]
       FROM myTable00
      WHERE ISDATE([date]) = 1
   GROUP BY [name]) AS xxx
       ON a.[name] = xxx.[name] AND a.[date] = xxx.[date]
ORDER BY RowId
GO

SET NOCOUNT OFF
DROP TABLE myTable99, myTable00
GO

2. This is the post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56303 for asking how to combine address information

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(
   CustLastName varchar(50)
 , Prefix varchar(3)
 , StreetNumber varchar(15)
 , StreetName varchar(50)
 , City   varchar(50)
 , State  varchar(2)
 , PostalCode  varchar(10))
GO

INSERT INTO myTable99(CustLastName, Prefix, StreetNumber, StreetName, City, State, PostalCode)
SELECT 'Kay',   'Mr.', '213', 'Lincoln Street', 'Newark', 'NJ', '07102-2992' UNION ALL
SELECT 'Smith', 'Mr.', '123', 'Main Street',    'Boston', 'MA', '12345'
GO

CREATE VIEW myView99
AS
SELECT    Prefix + ' ' + CustLastName    + CHAR(13) + CHAR(10)
 + StreetNumber + ' ' + StreetName + CHAR(13) + CHAR(10)
 + City + ', ' + State + '  ' + PostalCode AS AdressedTo
  FROM myTable99
GO

DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp Northwind.dbo.myView99 OUT d:\myView99.csv -T -c -SPAERSCBVD0014'
EXEC master..xp_cmdshell @cmd
GO

SET NOCOUNT OFF
DROP VIEW myView99
DROP TABLE myTable99
GO

3.  The poster willbourne asks in this post how to take all the data in a table, and based on it's [ID] column, make that a header, with all the descriptions appear as the “value”.  Normally a drop down box does this just fine, but they want to see the data like:

[1 ] [2 ] [3 ] [4 ]
[Eastern] [Western] [Northern] [Southern]

OK

USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(RegionID char(1), RegionDescription varchar(20))
GO

INSERT INTO myTable99(RegionID, RegionDescription)
SELECT '1', 'Eastern' UNION ALL
SELECT '2', 'Western' UNION ALL
SELECT '3', 'Northern' UNION ALL
SELECT '4', 'Southern'
GO

DECLARE @RegionID varchar(8000), @RegionDescription varchar(8000)

  SELECT @RegionID = COALESCE(@RegionId + ', ','') + RegionID
    FROM myTable99
ORDER BY RegionID
  SELECT @RegionDescription = COALESCE(@RegionDescription + ', ','') + RegionDescription
    FROM myTable99
ORDER BY RegionID

SELECT @RegionID
UNION ALL
SELECT @RegionDescription
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

 

 

 

 

Print | posted on Monday, October 10, 2005 5:05 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: To the chagrin of Jeff or How to format data in T-SQL

Brett,

I found your mailing labels SQL code very useful. Thanks for posting it. One thing it doesn't do is provide a clear record delimiter between addresses... any thoughts on that?

Terry
2/2/2006 1:36 PM | Terry Grignon
Gravatar

# re: To the chagrin of Jeff or How to format data in T-SQL

What type of delimiter are you looking for, one the separates the address group?

You could just add anything you want after the postal code.

Why not post a question on SQL team and post some sample data and expected results.

You should get an answer in minutes.

Thanks for reading.

Brett
2/3/2006 6:05 PM | Brett
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET