May 10, 2018 at 7:17 pm
I'm lost here. Maybe I'm making this out to be more difficult than it really is, but here's the problem that I can't wrap my head around. I need the first row of my output to be a header row (example: aaaaaaaaaaaaaa|bbbbbbbbbbbbbb|ccccccccccccc|0000000000|111111111111). The second row needs to be the data from the SQL query. I can't use a union statement because I have 20 columns that are piped in my statement, but the header has only "5" piped values. Also, as it's already been pointed out by the SQL, but let me state it anyways. The header row doesn't match the output data. What can I do?
I guess if you want to see SQL, it would be:SELECT 'aaaaaaaaaaaaaa|bbbbbbbbbbbbbb|ccccccccccccc|0000000000|111111111111';
SELECT [col1] + '|' + [col2] + '|' + [col3] + '|' + [col4] + '|' + [col5] + '|' + [col6] + '|' + [col7] + '|' + [col8]
+ '|' + [col9] + '|' + [col10] + '|' + [col11] + '|' + [col12] + '|' + [col13] + '|' + [col14] + '|' + [col15]
FROM
[Table1]
JOIN
[Table2]
ON [Table1].[Col1] = [Table2].[Col2];
May 10, 2018 at 10:32 pm
SQL_Enthusiast - Thursday, May 10, 2018 7:17 PMI'm lost here. Maybe I'm making this out to be more difficult than it really is, but here's the problem that I can't wrap my head around. I need the first row of my output to be a header row (example: aaaaaaaaaaaaaa|bbbbbbbbbbbbbb|ccccccccccccc|0000000000|111111111111). The second row needs to be the data from the SQL query. I can't use a union statement because I have 20 columns that are piped in my statement, but the header has only "5" piped values. Also, as it's already been pointed out by the SQL, but let me state it anyways. The header row doesn't match the output data. What can I do?I guess if you want to see SQL, it would be:
SELECT 'aaaaaaaaaaaaaa|bbbbbbbbbbbbbb|ccccccccccccc|0000000000|111111111111';SELECT [col1] + '|' + [col2] + '|' + [col3] + '|' + [col4] + '|' + [col5] + '|' + [col6] + '|' + [col7] + '|' + [col8]
+ '|' + [col9] + '|' + [col10] + '|' + [col11] + '|' + [col12] + '|' + [col13] + '|' + [col14] + '|' + [col15]
FROM
[Table1]
JOIN
[Table2]
ON [Table1].[Col1] = [Table2].[Col2];
You have to manually build the header row
SELECT 'aaaaaaaaaaaaaa|bbbbbbbbbbbbbb|ccccccccccccc|0000000000|111111111111'
UNION ALL
SELECT [col1] + '|' + [col2] + '|' + [col3] + '|' + [col4] + '|' + [col5] + '|' + [col6] + '|' + [col7] + '|' + [col8]
+ '|' + [col9] + '|' + [col10] + '|' + [col11] + '|' + [col12] + '|' + [col13] + '|' + [col14] + '|' + [col15]
FROM
[Table1]
JOIN
[Table2]
ON [Table1].[Col1] = [Table2].[Col2];
May 11, 2018 at 8:35 am
That doesn't work because the header row has 5 columns and the select statement has 15 columns. They both have to have 15 in order to work with a union. I'm going to have to move to SSIS and create then append... 🙁
Msg 205, Level 16, State 1, Line 15
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
May 11, 2018 at 8:39 am
SQL_Enthusiast - Friday, May 11, 2018 8:35 AMThat doesn't work because the header row has 5 columns and the select statement has 15 columns. They both have to have 15 in order to work with a union. I'm going to have to move to SSIS and create then append... 🙁Msg 205, Level 16, State 1, Line 15
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
That was an example. You will need to build the header using your own data.
May 11, 2018 at 8:47 am
If your data has 20 columns why is the header only 5 columns?
May 11, 2018 at 9:08 am
The next question is this, what are attempting to accomplish?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply