Scripting beginner

Question

Post reply

Scripting beginner

Mat-1147099

SSC Enthusiast

Points: 131
More actions
October 18, 2009 at 1:58 am

#227152

Hi there
I have been given a task to take a SQL Server DB and create some output in Excel. Data in the DB are coming through in a single field, which then needs to be split further into other fields depending on the response. e.g.
100000 washing machine
100000 hair dryer
100001 washing machine
100001 hair dryer
to:
100000 washing machine hairdryer
100001 washing machine hairdryer
Has anyone got a spare min or two to help me with some basic scripting for this?
Many thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Allister Reid SSCrazy Points: 2666 More actions · Answer 1

Any chance you could post some table definitions and sample data please?

It'll be a lot easier to try and help!

See following article how to do that http://www.sqlservercentral.com/articles/Best+Practices/61537/

Mat-1147099 SSC Enthusiast Points: 131 More actions · Answer 2

Sure, thanks

PropRef (unique property reference), is the primary key (INT, 6).

AttributeName, is the attribute name (VARCHAR, 50)

AttributeValue, is the value for the attribute (VARCHAR, 50)

Propref AttributeName AttrtibuteValue

100058 Heating Type Electric Heater

100058 Heating Product General Electric

100078 Heating Type Electric Heater

100078 Heating Product Mawson Electric - 2500W

From the above format to:

Propref Heating Type Heating Product

100058 Electric Heater General Electric

100078 Electric Heater Mawson Electric - 2500W

I hope this makes sense

Cheers

Mat

Allister Reid SSCrazy Points: 2666 More actions · Answer 3

Hi Mat,

yeah, a little clearer.

DECLARE @Temp TABLE

(

PropRef INT,

AttributeName VARCHAR(50),

AttributeValue VARCHAR(50)

)

INSERT INTO @Temp (Propref, AttributeName, AttributeValue)

SELECT 100058, 'Heating Type', 'Electric Heater' UNION ALL

SELECT 100058, 'Heating Product', 'General Electric' UNION ALL

SELECT 100078, 'Heating Type', 'Electric Heater' UNION ALL

SELECT 100078, 'Heating Product', 'Mawson Electric - 2500W'

SELECT

Propref,

MAX(CASE WHEN AttributeName = 'Heating Type' THEN AttributeValue END) AS [Heating Type],

MAX(CASE WHEN AttributeName = 'Heating Product' THEN AttributeValue END) AS [Heating Product]

FROM @Temp

GROUP BY Propref

Please notice I posted data in readily consumable format, it only takes a minute or two, and you make it easier for folks to help!

Allister

Mat-1147099 SSC Enthusiast Points: 131 More actions · Answer 4

Hi thanks Alistair

Only problem is it comes from a table with around 10,000 entries. The table name is called Component Assessments. All these entries have to be extracted and re-formatted into another table. Once it is in this table I will pull out using Crystal Reports. Have you got any ideas on how I can do this?

Many thanks

Mat:-)

Allister Reid SSCrazy Points: 2666 More actions · Answer 5

Hi Matt,

sorry, I'm not clear on what you need here. Are you having trouble with the query for creating the intermediate table or getting that table into Crystal Report?

If it's the former can you post sample data and the output you are needing?

If you are having trouble with Crystal reports I'm afraid I haven't used these, so wouldn't be much help!

A

Allister Reid SSCrazy Points: 2666 More actions · Answer 6

Is the problem that there are so many values for AttributeName, or you don't know up front which AttributeNames need to be in the result?

Mat-1147099 SSC Enthusiast Points: 131 More actions · Answer 7

Hi Alistair

Just the first part - getting the data into another table. I can port through to Crystal.

That field has many potential values.

Cheers

Mat

Mat-1147099 SSC Enthusiast Points: 131 More actions · Answer 8

Hi Alistair

Just the first part - getting the data into another table. I can port through to Crystal.

That field has many potential values.

Cheers

Mat

Allister Reid SSCrazy Points: 2666 More actions · Answer 9

Then you need a dynamic sql:

use tempdb

CREATE TABLE #Temp

(

PropRef INT,

AttributeName VARCHAR(50),

AttributeValue VARCHAR(50)

)

INSERT INTO #Temp (Propref, AttributeName, AttributeValue)

SELECT 100058, 'Heating Type', 'Electric Heater' UNION ALL

SELECT 100058, 'Heating Product', 'General Electric' UNION ALL

SELECT 100078, 'Heating Type', 'Electric Heater' UNION ALL

SELECT 100078, 'Heating Product', 'Mawson Electric - 2500W'

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = N'SELECT

Propref'

SELECT @sql = @sql + N',

MAX(CASE WHEN AttributeName = ''' + AttributeName + N''' THEN AttributeValue END) AS ' + QUOTENAME(AttributeName)

FROM (SELECT DISTINCT AttributeName FROM #Temp) AttributeNameList

SELECT @sql = @sql + N'

FROM #Temp

GROUP BY Propref'

PRINT @sql

EXEC (@Sql)

Hope this helps!

Allister

///Edit - added missing 'N'

happycat59 One Orange Chip Points: 29389 More actions · Answer 10

To me, this looks like a data presentation issue that really you be handled by Crystal Reports.

Personally, I am not all that familiar with Crystal Reports, but the equivalent in Reporting Services is a matrix report. It is designed to have data that looks like what you have in your database and pivot the data based on, this case, AttributeName.