CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1))
INSERT INTO test values(111,'a12','a')
INSERT INTO test values(111,'d33','a')
INSERT INTO test values(222,'a25','a')
INSERT INTO test values(222,'g21','e')
INSERT INTO test values(333,'a65','a')
INSERT INTO test values(333,'a64','e')
INSERT INTO test values(444,'w11','f')
INSERT INTO test values(555,'a41','a')
INSERT INTO test values(555,'r99','a')
INSERT INTO test values(555,'a76','e')
I need to output the records where the same material has an 'A' as the first
character in col1 and col2 has both an 'A' and an 'E'. So the output should
look like this:
Material col1 col2
333 a65 a
333 a64 e
555 a41 a
555 a76 e
TIAWithout better specs, my first guess is:
SELECT material, MIN(col1), col2
FROM test
GROUP BY material, col2
ORDER BY material, col2
Now, you say you want the output to be those 4 rows. Are those the ONLY
rows that should be returned? If so, why are you eliminating the rows where
material = 111, for example?
Please see http://www.aspfaq.com/5006 for some information on providing
requirements that make follow-up questions, and a delayed solution, much
less likely...
A
"Chesster" <Chesster@.discussions.microsoft.com> wrote in message
news:1DA8479F-3093-40D0-B63D-C2B7630D0DA8@.microsoft.com...
> CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1))
> INSERT INTO test values(111,'a12','a')
> INSERT INTO test values(111,'d33','a')
> INSERT INTO test values(222,'a25','a')
> INSERT INTO test values(222,'g21','e')
> INSERT INTO test values(333,'a65','a')
> INSERT INTO test values(333,'a64','e')
> INSERT INTO test values(444,'w11','f')
> INSERT INTO test values(555,'a41','a')
> INSERT INTO test values(555,'r99','a')
> INSERT INTO test values(555,'a76','e')
> I need to output the records where the same material has an 'A' as the
> first
> character in col1 and col2 has both an 'A' and an 'E'. So the output
> should
> look like this:
> Material col1 col2
> 333 a65 a
> 333 a64 e
> 555 a41 a
> 555 a76 e
> TIA
>|||Yes, those are the only rows that should be returned.
Rows 111 should not be in the output because col2 does not have both an 'A'
and an 'E'.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Without better specs, my first guess is:
> SELECT material, MIN(col1), col2
> FROM test
> GROUP BY material, col2
> ORDER BY material, col2
> Now, you say you want the output to be those 4 rows. Are those the ONLY
> rows that should be returned? If so, why are you eliminating the rows whe
re
> material = 111, for example?
> Please see http://www.aspfaq.com/5006 for some information on providing
> requirements that make follow-up questions, and a delayed solution, much
> less likely...
> A
>
> "Chesster" <Chesster@.discussions.microsoft.com> wrote in message
> news:1DA8479F-3093-40D0-B63D-C2B7630D0DA8@.microsoft.com...
>
>|||SET NOCOUNT ON;
GO
CREATE TABLE test
(
material INT,
col1 VARCHAR(3),
col2 VARCHAR(1)
);
GO
INSERT test SELECT 111,'a12','a';
INSERT test SELECT 111,'d33','a';
INSERT test SELECT 222,'a25','a';
INSERT test SELECT 222,'g21','e';
INSERT test SELECT 333,'a65','a';
INSERT test SELECT 333,'a64','e';
INSERT test SELECT 444,'w11','f';
INSERT test SELECT 555,'a41','a';
INSERT test SELECT 555,'r99','a';
INSERT test SELECT 555,'a76','e';
SELECT t.material, MIN(t.col1), t.col2
FROM test t
WHERE
LEFT(t.col1,1) = 'a'
AND
(
(col2 = 'a' AND EXISTS
(
SELECT 1 FROM test tA
WHERE tA.material = t.material
AND LEFT(tA.col1,1) = 'a'
AND col2 = 'e'
))
OR
(col2 = 'e' AND EXISTS
(
SELECT 1 FROM test tB
WHERE tB.material = t.material
AND LEFT(tB.col1,1) = 'a'
AND col2 = 'a'
))
)
GROUP BY material, col2
ORDER BY material, col2;
GO
DROP TABLE test;
GO
"Chesster" <Chesster@.discussions.microsoft.com> wrote in message
news:854D3331-2645-4F01-8204-86AABF15AD91@.microsoft.com...
> Yes, those are the only rows that should be returned.
> Rows 111 should not be in the output because col2 does not have both an
> 'A'
> and an 'E'.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||select * from #test where [material] in(
select ta.[material] from (
select [material] from #test where col1 like 'a%' and col2='a') ta
join (
select [material] from #test where col1 like 'a%' and col2='e') te
on ta.[material] = te.[material]
)
and col2 in('a','e')
material col1 col2
-- -- --
333 a65 a
333 a64 e
555 a41 a
555 r99 a
555 a76 e
I see that
555 r99 a
is not present in your sample output. I don't see why it does not
belong in the result set|||> I see that
> 555 r99 a
> is not present in your sample output. I don't see why it does not
> belong in the result set
Because the first letter of col1 != 'a'|||thanks Aaron.
select * from #test t1
where col1 like 'a%'
and col2 in('a','e')
and (select count(*) from #test t2
where t1.[material] = t2.[material]
and t2.col1 like 'a%'
and t2.col2 in('a','e') and t2.col1<>t1.col1)>0
material col1 col2
-- -- --
333 a65 a
333 a64 e
555 a41 a
555 a76 e
(4 row(s) affected)|||select * from #test where [material] in(
select ta.[material] from (
select [material] from #test where col1 like 'a%' and col2='a') ta
join (
select [material] from #test where col1 like 'a%' and col2='e') te
on ta.[material] = te.[material]
)
and col2 in('a','e') and col1 like 'a%'
just added col1 like 'a%' at the end.
Regards
Showing posts with label output. Show all posts
Showing posts with label output. Show all posts
Friday, March 9, 2012
Monday, February 20, 2012
Need help with XML output to file
I'm using SQL Server 2005 / 9.0.3042
I'm not new to sql server, but making my first experience with xml in sql server 2005.
I have a query like this (based on <Table> with neccessary data):
SELECT TAG, PARENT, <columns...>
FROM <Table>
FOR XML EXPLICIT
This query creates a xml file exactly as i need it when i execute it in Management Studio. Well, with one exception. It does not write the <xml...> tag at the beginning of the xml file. But i'm sure i get that in there somewho else. What i need to do now is get that output to a file on disk. And that's where my problem starts.
I tried SQLCMD within Management Studio, but that doesn't accept the ':XML ON' tag and ignores it. the resulting file written is not usable, as it also contains query summary information.
Any direction would be greatly appreciated!
Have you given DTS/SSIS a try. If you are having to do this procedure often, I would go with one of those.
|||I could not find any way to choose xml as the destination for ssis. Could you give me a start on how to go on?
|||Hi Danny,As you said that you are facing this problem in sql2005, so can u please tell me the querry by which i can generate a xml file through table in sql2000,
My basic question to you is,
HOW TO GENERATE AN XML FILE USING A SQL QUERY IN QUERY ANALYZER.?
is it possible.|||
Hi Prashant,
There are various ways to create a xml file. In general, u create a select statement and use "FOR XML xxxxx" at the end. Please have a look in Books Online for the possible <xxxxxx>. I would say it depends on purpose u want to achieve, you would choose the appropriate <xxxxxx> method. For each method u need to have its own data base.
I for my case needed to choose the FOR XML EXPLICIT method, as i need to reproduce a specific xml file dynamically, based on certain data. Running the query will create a xml file and give it as the result, so i can open it, and if i need copy the content. If "FOR XML EXPLICIT" is your choice, here is a simple example. I haven't done much on the other <xxxxxx> methods, so i'm sorry i won't be much of help. Method FOR XML EXPLICIT is the most time consuming way, but it gives almost every control to produce exactly the xml file needed.
OK: Here the sample for FOR XML EXPLICIT:
Let's suppose we need a xml file like this:
<Order>
<OrderItem Title="book1" Price="250.00"/>
<OrderItem Title="book2" Price="15.75" Discount=5.00/>
</Order>
For this we need to create a table holding the data to create the xml file using FOR XML EXPLICIT. This table must look like this: In the vertical it will have 1 row for ea line in the xml file. in the horizontal it needs the sum of all possible attributes. Enter a value will print the value in the xml file, enter empty string will print empty string in xml file, enter NULL as value will remove the attribute in the xml file. The table also needs the informatione to tell FOR XML EXPLICIT how the hierachy of the xml file must be. That is done using the TAG and PARENT attribut. The columns in the table must exactly match the names of the elements and attributes in the xml file, plus the level (number between - see blow).
Ok, here is the table:
TAG PARENT [Order!1] [OrderItem!2!Title] [OrderItem!2!Prive] [OrderItem!2!Discount]
-
1 NULL '' NULL NULL NULL
2 1 NULL book1 250.00 NULL
3 1 NULL book2 15.75 5.00
-
This is only a very simple example of FOR XML EXPLICIT, but i hope it makes clear on how it works. I used this way to generate our xml files dynamically. It was much work to build the system, but gives me much flexibility to construct all the various different xml files. Last but not least, it's only useful if the structure of the xml file don't change so often.
Need help with XML output to file
I'm using SQL Server 2005 / 9.0.3042
I'm not new to sql server, but making my first experience with xml in sql server 2005.
I have a query like this (based on <Table> with neccessary data):
SELECT TAG, PARENT, <columns...>
FROM <Table>
FOR XML EXPLICIT
This query creates a xml file exactly as i need it when i execute it in Management Studio. Well, with one exception. It does not write the <xml...> tag at the beginning of the xml file. But i'm sure i get that in there somewho else. What i need to do now is get that output to a file on disk. And that's where my problem starts.
I tried SQLCMD within Management Studio, but that doesn't accept the ':XML ON' tag and ignores it. the resulting file written is not usable, as it also contains query summary information.
Any direction would be greatly appreciated!
Have you given DTS/SSIS a try. If you are having to do this procedure often, I would go with one of those.
|||I could not find any way to choose xml as the destination for ssis. Could you give me a start on how to go on?
|||Hi Danny,As you said that you are facing this problem in sql2005, so can u please tell me the querry by which i can generate a xml file through table in sql2000,
My basic question to you is,
HOW TO GENERATE AN XML FILE USING A SQL QUERY IN QUERY ANALYZER.?
is it possible.|||
Hi Prashant,
There are various ways to create a xml file. In general, u create a select statement and use "FOR XML xxxxx" at the end. Please have a look in Books Online for the possible <xxxxxx>. I would say it depends on purpose u want to achieve, you would choose the appropriate <xxxxxx> method. For each method u need to have its own data base.
I for my case needed to choose the FOR XML EXPLICIT method, as i need to reproduce a specific xml file dynamically, based on certain data. Running the query will create a xml file and give it as the result, so i can open it, and if i need copy the content. If "FOR XML EXPLICIT" is your choice, here is a simple example. I haven't done much on the other <xxxxxx> methods, so i'm sorry i won't be much of help. Method FOR XML EXPLICIT is the most time consuming way, but it gives almost every control to produce exactly the xml file needed.
OK: Here the sample for FOR XML EXPLICIT:
Let's suppose we need a xml file like this:
<Order>
<OrderItem Title="book1" Price="250.00"/>
<OrderItem Title="book2" Price="15.75" Discount=5.00/>
</Order>
For this we need to create a table holding the data to create the xml file using FOR XML EXPLICIT. This table must look like this: In the vertical it will have 1 row for ea line in the xml file. in the horizontal it needs the sum of all possible attributes. Enter a value will print the value in the xml file, enter empty string will print empty string in xml file, enter NULL as value will remove the attribute in the xml file. The table also needs the informatione to tell FOR XML EXPLICIT how the hierachy of the xml file must be. That is done using the TAG and PARENT attribut. The columns in the table must exactly match the names of the elements and attributes in the xml file, plus the level (number between - see blow).
Ok, here is the table:
TAG PARENT [Order!1] [OrderItem!2!Title] [OrderItem!2!Prive] [OrderItem!2!Discount]
-
1 NULL '' NULL NULL NULL
2 1 NULL book1 250.00 NULL
3 1 NULL book2 15.75 5.00
-
This is only a very simple example of FOR XML EXPLICIT, but i hope it makes clear on how it works. I used this way to generate our xml files dynamically. It was much work to build the system, but gives me much flexibility to construct all the various different xml files. Last but not least, it's only useful if the structure of the xml file don't change so often.
Subscribe to:
Posts (Atom)