Generate one line SQL Script

[#1]

Hi qvcommunity!

I am often in need to generate SQL Scripts for an ETL we use. I'd love to use qv as a SQL Script generator.

The idea is to use the sqltable function to read meta data and then using qv functions to change the name of e.g. columns.

My question is how could i flatten a table in order to output one string and add e.g. a CREATE TABLE Statement?

Any Ideas?

Thanks

Andi

[pover]

Hello Andi,

Can you upload a sample of you metadata and the result you want so as to better understand your question?

You might also download and install the QV SAP Connector which contains a QV SAP ScriptBuillder that uses SAP metadata to contruct QlikView Load Script. It might give you some ideas for creating your own SQL Scriptbuilder.

Regards.

[#3]

Hey Karl!

Thanks for the hint. I will have a look at the SAP Connector.

In general the problem is as following.

I've got n tables with n rows and 2 columns namely ID, ColumnName.

The table looks like:

=======Orders =====

ID; Column

==================

1; OrderID

2; OrderItem

3; Order Amount

4; .........

=======Customers =====

ID; Column

==================

1; Customer_ID

2; CustomerName

3; Customer......

The output should be 1 Table with

=====SQL_Queries=====

Tablename; SQL_Query

====================

Orders; SELECT Order_Id, OrderItem, OrderAmount, ... FROM Orders

Customers; SELECT Customer_ID, CustomerName, Customer.... FROM Customers

So precisly my question really is how to pivot a column to a row.

Thanks for suggestions

Andi

[pover]

Andi,

Here is an example of what I think you are looking for. I added a third column which is the table name. If you don't have that information in the table you should be able to generate it in the QV Load Script with one of the Table Functions you can find in QV Help.

Regards.