sql puzzle - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql puzzle

Author

Topic

NTC
Starting Member

21 Posts

Posted - 2006-12-27 : 16:00:26

my puzzle is that a record has a single linear set of fields - - and I need it reformatted for an accounting program to import that requires a fixed structure that is multi line. Want to reformat by making a temp table - then can export to excel or tab delimited.

What I have:

Record1: Field1 Field2 Field3

What I need:

Header1a Header1b Header1c Header1d

Header2a Header2b Header2c Header2d

Header3a Field1 (blank) Field2

Header4a (blank) (blank) Field3

Header5a

Have tried to line up into 4 columns. The specifics are not important...but the general idea is that there are a couple lines of fixed data which I call Headers. A header starts each line and a header ends it.

All the headers are fixed data and I have them. There are blank fields as in some records there is no data and that is ok when it does occur.

So to sum up: There is a query that returns to the user the correct record. Next there needs to be a 'prepare to export' button that will trigger an sql resulting in a temp table with this format....

And it is the sql that is puzzling me. Am mulling over an sql statement that will work and welcome very much a sample or two from you full time sql-ers out there to help me get my brain around it.....gracias.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-12-27 : 17:16:15

Not exactly sure what you want, but maybe this will help. It essentially "de-crosstabs" a dataset, converting repeated columns into separate records:

create function NormalizeXTabSQL(@TableName varchar(500), @ColumnIncrement int, @StartColumn int, @EndColumn int)
returns varchar(4000)
as
begin
--NormalizeXTab
--blindman, 2/4/2005
--Returns a SQL UNION statement that converts repeating
--column groups in a table into normalized individual records.

--Test parameters
-- declare @TableName varchar(500)
-- declare @ColumnIncrement int
-- declare @StartColumn int
-- declare @EndColumn int
-- set	@TableName = 'LimsImportStaging' --Name of your target table
-- set	@ColumnIncrement = 3  --Number of columns to return for each statement
-- set	@StartColumn = 2
-- set	@EndColumn = 16

declare	@ColumnString varchar(500)
declare	@ColumnCounter int
declare	@SQLString varchar(4000)

set     @ColumnCounter = isnull(@StartColumn, 1) - 1

set     @EndColumn = isnull(@EndColumn,
        (select	count(*)
        from	sysobjects
                inner join syscolumns on sysobjects.id = syscolumns.id
        where	sysobjects.name = @TableName))

while   @ColumnCounter < @EndColumn
begin
        set	@ColumnString = null
        set	@ColumnCounter = @ColumnCounter + @ColumnIncrement

        select	@ColumnString = isnull(@ColumnString + ', ', '') + syscolumns.name
        from	sysobjects
                inner join syscolumns on sysobjects.id = syscolumns.id
        where	sysobjects.name = @TableName
                and colid > @ColumnCounter - @ColumnIncrement
                and colid <= @ColumnCounter
                and colid <= @EndColumn
        order by colid
	
        set     @SQLString = isnull(@SQLString + char(13) + 'UNION' + char(13), '') + 'select ' + @ColumnString  + ' from ' + @TableName
end

return	@SQLString

end

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-27 : 17:21:18

How about

SELECT 'Header1a', 'Header1b', 'Header1c', 'Header1d' UNION ALL
SELECT 'Header2a', 'Header2b', 'Header2c', 'Header2d' UNION ALL
SELECT 'Header3a', Field1, NULL, Field2 FROM table WHERE criteria UNION ALL
SELECT 'Header4a', NULL, NULL, Field3 FROM table WHERE criteria UNION ALL
SELECT 'Header5a', NULL, NULL, NULL

NTC
Starting Member

21 Posts

Posted - 2006-12-27 : 18:42:18

Blindman - thanks for that - it is going to take me some time to noodle around with your code.

snSQL - thanks also - question: is that 3rd/4th line approach ok?

Header is fixed data that I have. It is not in any table (yet) - (though I can make a table to hold the Header data if need be).

While of course the Fields are data from Table of Record1

Can one Select in the manner you show with just entering in the Header4a data right into the Select statement? Never have seen that.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-27 : 19:21:38

Yes, you can use literal values in any column in a SELECT.

BTW, I should have added that you can also use your own column names, just put the aliases in the first SELECT like this

SELECT 'Header1a' AS Col1, 'Header1b' AS Col2, 'Header1c' AS Col3, 'Header1d' AS Col4 UNION ALL
SELECT 'Header2a', 'Header2b', 'Header2c', 'Header2d' UNION ALL
SELECT 'Header3a', Field1, NULL, Field2 FROM table WHERE criteria UNION ALL
SELECT 'Header4a', NULL, NULL, Field3 FROM table WHERE criteria UNION ALL
SELECT 'Header5a', NULL, NULL, NULL

NTC
Starting Member

21 Posts

Posted - 2006-12-27 : 20:43:12

very helpful - much thanks...am going to give it a dry run and see how things go.

NTC
Starting Member

21 Posts

Posted - 2006-12-27 : 21:39:23

can't build it ....first line is ok
SELECT 'Header1a'

but can't get
SELECT 'Header1a'
SELECT 'Header2a'

with or without the UNION ALL

maybe an insert method is something I should consider....

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-27 : 21:48:49

show what you actually tried...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

NTC
Starting Member

21 Posts

Posted - 2006-12-27 : 22:22:01

first built first line ok
SELECT 'Header1a', 'Header1b', etc
returned fine with all the fixed data I entered

then could not add second line....so stripped it down and tried alot of variations...could never get a 2 line returned:

SELECT 'Header1a' UNION ALL
SELECT 'Header2a' ;

SELECT 'Header1a' UNION ALL
SELECT 'Header2a' UNION ALL;

SELECT 'Header1a'
SELECT 'Header2a' ;

Not sure if JetSQL or me or what...but could never get the needed first two lines of fixed data that is needed....

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-28 : 00:02:31

How are you running that code, are you even using SQL Server?

The code I gave you returns this on SQL Server

Col1     Col2     Col3     Col4
-------- -------- -------- ----------------------------------------
Header1a Header1b Header1c Header1d
Header2a Header2b Header2c Header2d
Header3a ALFKI    NULL     Maria Anders
Header4a NULL     NULL     Alfreds Futterkiste
Header5a NULL     NULL     NULL

NTC
Starting Member

21 Posts

Posted - 2006-12-28 : 23:05:59

Is Access

If I use the 1st sql line - this works by itself:
SELECT 'Header1a' AS Col1, 'Header1b' AS Col2, 'Header1c' AS Col3, 'Header1d' AS Col4 (without the UNION ALL since is single line)

But if I add the second line - error states that a 'table or query is needed in statement'

If I use the 3rd sql line - this works stand alone:
SELECT 'Header3a', Field1, NULL, Field2 FROM table WHERE criteria

But won't work under the 1st line - same error message.....

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-29 : 04:08:50

hmmmm... well that explains it. The query I gave you works fine on SQL Server, but I think you have to have a FROM clause in Access. You could try asking about that in the Access forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources