Export in txt from sql (in a pivot way)

Question

Export in txt from sql (in a pivot way)

antonela

Ten Centuries

Points: 1007
More actions
May 23, 2014 at 8:17 am

#396805

Hello everybody,
I have to export the records of a table in a .txt file but in a strange way (in a column). For example I have this table with 3 fields:
create table prova
(cod varchar(1),
des1 varchar(2),
des2 Varchar (2)
)
insert into prova values (1, 'A1', 'A2')
insert into prova values (2, 'B1', 'B2')
I need to have the output in this way:
1 cod 1
1 des1 A1
1 des2 A2
8 S
1 cod 2
1 des1 B1
1 des2 B2
8 S
So, I have to put '1' at the begining of every field and '8 S' at the end of every record.
Anyboody helps me to do this?
Thank you very much.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

antonela (5/23/2014)
Hello everybody,
I have to export the records of a table in a .txt file but in a strange way (in a column). For example I have this table with 3 fields:
create table prova
(cod varchar(1),
des1 varchar(2),
des2 Varchar (2)
)
insert into prova values (1, 'A1', 'A2')
insert into prova values (2, 'B1', 'B2')
I need to have the output in this way:
1 cod 1
1 des1 A1
1 des2 A2
8 S
1 cod 2
1 des1 B1
1 des2 B2
8 S
So, I have to put '1' at the begining of every field and '8 S' at the end of every record.
Anyboody helps me to do this?
Thank you very much.

It is hard to figure out exactly what you want for output here. This should at least get you started.

select d.Val

from prova

cross apply (values('1 cod ' + Cod),('1 des1 ' + des1),('1 des2 ' + des2),('8 S')) d(Val)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

antonela Ten Centuries Points: 1007 More actions · Answer 2

antonela

Ten Centuries

Points: 1007

May 23, 2014 at 3:04 pm

#1716269

Thank you very much.

antonela Ten Centuries Points: 1007 More actions · Answer 3

Do you know how can I do the same in sql server 2005?

This functions properly only in sql server 2008 and 2012.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

The alternative could be the official unpivot.

SELECT Val

FROM (SELECT CAST( '1 cod ' + CAST( cod AS varchar(10)) AS varchar(20)) col1,

CAST( '1 des1' + des1 AS varchar(20)) col2,

CAST( '1 des2' + des2 AS varchar(20)) col3,

CAST( '8 S' AS varchar(20)) col4

FROM #prova) p

UNPIVOT(Val FOR Cols IN (col1, col2, col3, col4))u

Or you could use UNION ALL for 2000 and previous versions.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

antonela (5/26/2014)
Do you know how can I do the same in sql server 2005?
This functions properly only in sql server 2008 and 2012.

Heh... this is why you should always pick the correct forum to post your questions on. Because you posted on 2012 forum, people just naturally assume that your question is based on 2012 instead of 2005. Posting on the correct forum means that you'll get a compatible answer instead of having to wait for someone to rehash your code for an earilier version.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

antonela Ten Centuries Points: 1007 More actions · Answer 6

You're right.

Unfortunately when I wrote the first post I didn't know the customer still uses 2005.

antonela Ten Centuries Points: 1007 More actions · Answer 7

antonela

Ten Centuries

Points: 1007

May 27, 2014 at 3:44 am

#1716634

Thank you very much

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

antonela (5/27/2014)
You're right.
Unfortunately when I wrote the first post I didn't know the customer still uses 2005.

Ah... got it. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

antonela Ten Centuries Points: 1007 More actions · Answer 9

For me is very important to have the right order in the table.

For the first record I must to have the value of the first field of my original table, in the last record I must to have '8 S' that indicates me I have the fix value I put (but for the last record).

Is it possible to put another field (count) :1, 2, 3 etc?

val count

1 cod 1 1

1 des1 A1 2

1 des2 A2 3

8 S 4

1 cod 2 5

1 des1 B1 6

1 des2 B2 7

8 S 8

When I have many records I am not sure I have the right sorting.

I insert the result of the query in a table and when I call "select top 100 percent * from table" I don't have '8 S' on the last record.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

Order in a table is not guaranteed. Actually, it does not exists in a logical sense.

You could force it with an identity column and being sure the inserts follow the order that you need. Otherwise, you won't have a defined order as you would on a text file.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

To add to Luis' comment about ordering. You would need some sort of column like an identity but when you select the data you need to add an ORDER BY to your query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sgmunson SSC Guru Points: 110640 More actions · Answer 12

How about the following:

IF OBJECT_ID(N'PROVA') IS NOT NULL DROP TABLE PROVA

CREATE TABLE PROVA (

cod varchar(2),

des1 varchar(2),

des2 Varchar(2)

)

INSERT INTO PROVA VALUES (1, 'A1', 'A2')

INSERT INTO PROVA VALUES (2, 'B1', 'B2')

;WITH COLORDER AS (

SELECT COLUMN_NAME, ORDINAL_POSITION

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'PROVA'

),

PROVB AS (

SELECT *, cod AS RN

FROM PROVA

),

INITIAL_RESULTS AS (

SELECT 1 AS F1, FIELD_NAME, FIELD_VALUE, RN

FROM PROVB AS P

UNPIVOT (FIELD_VALUE FOR FIELD_NAME IN ([cod], [des1], [des2])) AS UPVT

),

FINAL_CTE AS (

SELECT IR.*, C.ORDINAL_POSITION, ROW_NUMBER() OVER(ORDER BY IR.RN, C.ORDINAL_POSITION) AS REC_NUM

FROM INITIAL_RESULTS AS IR

INNER JOIN COLORDER AS C

ON IR.FIELD_NAME = C.COLUMN_NAME

UNION ALL

SELECT 8 AS F1, 'S' AS FIELD_NAME, '' AS FIELD_VALUE, RN, 4 AS ORDINAL_POSITION, C2.ORDINAL_POSITION AS REC_NUM

FROM INITIAL_RESULTS AS IR2

INNER JOIN COLORDER AS C2

ON IR2.FIELD_NAME = C2.COLUMN_NAME

WHERE C2.ORDINAL_POSITION = 1

)

SELECT F1, FIELD_NAME, FIELD_VALUE

FROM FINAL_CTE

ORDER BY RN, ORDINAL_POSITION

It should run in SQL 2005, as that version does support CTEs.

antonela (6/13/2014)
For me is very important to have the right order in the table.
For the first record I must to have the value of the first field of my original table, in the last record I must to have '8 S' that indicates me I have the fix value I put (but for the last record).
Is it possible to put another field (count) :1, 2, 3 etc?
val count
1 cod 1 1
1 des1 A1 2
1 des2 A2 3
8 S 4
1 cod 2 5
1 des1 B1 6
1 des2 B2 7
8 S 8
When I have many records I am not sure I have the right sorting.
I insert the result of the query in a table and when I call "select top 100 percent * from table" I don't have '8 S' on the last record.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 13

antonela,

Any update on whether the ordering problem is resolved, and whether or not my post helped with that?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)