sql server programming:
INSERT commands are very hard to work with without the corresponding CREATE TABLE commands. Roy Harvey Beacon Falls, CT [quoted text, click to view] On Thu, 16 Aug 2007 23:28:44 GMT, "GB" <v7v1k3@hotmail.com> wrote:
>Hello,
>I have the table:
>
>INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-01-01
>00:00:00.000',121),'33','21','45','A ')
>INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-02-01
>00:00:00.000',121),'28','28','41','A ')
>INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-01-01
>00:00:00.000',121),'11','35','50','B ')
>INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-02-01
>00:00:00.000',121),'21','38','43','B ')
>
>I need a query to transform the table to the following:
>
>INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
>00:00:00.000',121),'K1 ','33','A ')
>INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
>00:00:00.000',121),'K1 ','28','A ')
>INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
>00:00:00.000',121),'K2 ','21','A ')
>INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
>00:00:00.000',121),'K2 ','28','A ')
>INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
>00:00:00.000',121),'K1 ','11','B ')
>INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
>00:00:00.000',121),'K1 ','21','B ')
>INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
>00:00:00.000',121),'K2 ','35','B ')
>INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
>00:00:00.000',121),'K2 ','38','B ')
>
>Thanks,
>GB
Hello,
I have the table:
INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-01-01
00:00:00.000',121),'33','21','45','A ')
INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-02-01
00:00:00.000',121),'28','28','41','A ')
INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-01-01
00:00:00.000',121),'11','35','50','B ')
INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-02-01
00:00:00.000',121),'21','38','43','B ')
I need a query to transform the table to the following:
INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
00:00:00.000',121),'K1 ','33','A ')
INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
00:00:00.000',121),'K1 ','28','A ')
INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
00:00:00.000',121),'K2 ','21','A ')
INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
00:00:00.000',121),'K2 ','28','A ')
INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
00:00:00.000',121),'K1 ','11','B ')
INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
00:00:00.000',121),'K1 ','21','B ')
INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
00:00:00.000',121),'K2 ','35','B ')
INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
00:00:00.000',121),'K2 ','38','B ')
Thanks,
GB
In SQL 2005 you can UNPIVOT: SELECT * FROM INTABLE UNPIVOT( K FOR xval In ( K1, K2, K3 ) ) upvt I get extra records than in your outtable - don't know if you missed them out? Let me know how you get on. wBob [quoted text, click to view] "GB" wrote:
> Hello,
> I have the table:
>
> INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-01-01
> 00:00:00.000',121),'33','21','45','A ')
> INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-02-01
> 00:00:00.000',121),'28','28','41','A ')
> INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-01-01
> 00:00:00.000',121),'11','35','50','B ')
> INSERT INTABLE(DATADATE,K1,K2,K3,IND) VALUES(convert(datetime,'2000-02-01
> 00:00:00.000',121),'21','38','43','B ')
>
> I need a query to transform the table to the following:
>
> INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
> 00:00:00.000',121),'K1 ','33','A ')
> INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
> 00:00:00.000',121),'K1 ','28','A ')
> INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
> 00:00:00.000',121),'K2 ','21','A ')
> INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
> 00:00:00.000',121),'K2 ','28','A ')
> INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
> 00:00:00.000',121),'K1 ','11','B ')
> INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
> 00:00:00.000',121),'K1 ','21','B ')
> INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
> 00:00:00.000',121),'K2 ','35','B ')
> INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
> 00:00:00.000',121),'K2 ','38','B ')
>
> Thanks,
> GB
>
>
Thank you Bob, it works fine. GB [quoted text, click to view] "Bob" <Bob@discussions.microsoft.com> wrote in message
news:A2DB7F09-2E4F-4152-BF4F-89150CE88490@microsoft.com...
> In SQL 2005 you can UNPIVOT:
>
> SELECT *
> FROM INTABLE
> UNPIVOT( K FOR xval In ( K1, K2, K3 ) ) upvt
>
> I get extra records than in your outtable - don't know if you missed them
out?
>
> Let me know how you get on.
>
> wBob
>
> "GB" wrote:
>
> > Hello,
> > I have the table:
> >
> > INSERT INTABLE(DATADATE,K1,K2,K3,IND)
VALUES(convert(datetime,'2000-01-01
> > 00:00:00.000',121),'33','21','45','A ')
> > INSERT INTABLE(DATADATE,K1,K2,K3,IND)
VALUES(convert(datetime,'2000-02-01
> > 00:00:00.000',121),'28','28','41','A ')
> > INSERT INTABLE(DATADATE,K1,K2,K3,IND)
VALUES(convert(datetime,'2000-01-01
> > 00:00:00.000',121),'11','35','50','B ')
> > INSERT INTABLE(DATADATE,K1,K2,K3,IND)
VALUES(convert(datetime,'2000-02-01
> > 00:00:00.000',121),'21','38','43','B ')
> >
> > I need a query to transform the table to the following:
> >
> > INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
> > 00:00:00.000',121),'K1 ','33','A ')
> > INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
> > 00:00:00.000',121),'K1 ','28','A ')
> > INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
> > 00:00:00.000',121),'K2 ','21','A ')
> > INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
> > 00:00:00.000',121),'K2 ','28','A ')
> > INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
> > 00:00:00.000',121),'K1 ','11','B ')
> > INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
> > 00:00:00.000',121),'K1 ','21','B ')
> > INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-01-01
> > 00:00:00.000',121),'K2 ','35','B ')
> > INSERT OUTTABLE(DATADATE,K,VAL,IND) VALUES(convert(datetime,'2000-02-01
> > 00:00:00.000',121),'K2 ','38','B ')
> >
> > Thanks,
> > GB
> >
> >
> >
Don't see what you're looking for? Try a search.
|
