August 27, 2013 at 2:13 pm
hi
this is my table
CREATE TABLE [dbo].[emp_new](
[empid] [int] NULL,
[primary1] [varchar](20) NULL,
[primaryinten1] [int] NULL,
[primary2] [varchar](20) NULL,
[primaryinten2] [int] NULL,
[primary3] [varchar](20) NULL,
[primaryinten3] [int] NULL,
[primary4] [varchar](20) NULL,
[primaryinten4] [int] NULL,
[primary5] [varchar](20) NULL,
[primaryinten5] [int] NULL,
[primary6] [varchar](20) NULL,
[primaryinten6] [int] NULL,
[primary7] [varchar](20) NULL,
[primaryinten7] [int] NULL
) ON [PRIMARY]
---------------------------------------------------------
insert into emp_new(empid,primary1,primaryinten1)
values(1,'ws',1)
insert into emp_new(empid,primary2,primaryinten2)
values(1,'gh',1)
insert into emp_new(empid,primary2,primaryinten2)
values(1,'lg',3)
insert into emp_new(empid,primary2,primaryinten2)
values(1,'fd',1)
insert into emp_new(empid,primary2,primaryinten2)
values(1,'hj',1)
----------------------------------------------------------------------
empidprimary1primaryinten1primary2primaryinten2primary3primaryinten3primary4primaryinten4primary5primaryinten5primary6primaryinten6primary7primaryinten7
1ws1NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1NULLNULLgh1NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1NULLNULLlg3NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1NULLNULLfd1NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1NULLNULLhj1NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
----------------------------------------------------------------------------------------
whati want is everything in 1 line.
empid primary1primaryinten1primary2primaryinten2primary3primaryinten3primary4primaryinten4primary5primaryinten5primary6primaryinten6primary7primaryinten7
1 ws 1 gh 1 lg 3 fd 1 hj 1 null null null null
------------------------------------------------------------------------------------------
and the rows rows should be delete.
there should be line where primary1 is not null ,that line should be filled with all other condition and other rows should be delete.
how to do it
August 27, 2013 at 2:50 pm
Nice job posting ddl and sample data. However you lost it with your description. It sound like you want a dynamic cross tab but some of your comments are very confusing.
You can read up on how to do a dynamic cross tab by following the link in my signature. It explains how to do this in great detail.
_______________________________________________________________
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/
August 28, 2013 at 7:52 am
the thing is i cannot use aggregate function in PIVOT , as i need to place character in the column
August 28, 2013 at 7:55 am
riya_dave (8/28/2013)
the thing is i cannot use aggregate function in PIVOT , as i need to place character in the column
You can use a character in an aggregate (MIN, MAX, etc...). You really should read the article I suggested. It demonstrates a way to do this without using PIVOT.
_______________________________________________________________
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/
August 28, 2013 at 12:07 pm
i am not finding a solution,please help me hwo to do it
August 28, 2013 at 12:51 pm
riya_dave (8/28/2013)
i am not finding a solution,please help me hwo to do it
What have you tried? I still have no idea what your actual desired output should look like.
_______________________________________________________________
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/
August 28, 2013 at 12:56 pm
ok
i tried this one
CREATE TABLE [dbo].[emp_new](
[empid] [int] NULL,
[primary1] [varchar](20) NULL,
[primaryinten1] [int] NULL,
[secondary2] [varchar](20) NULL,
[secondaryinten2] [int] NULL,
[secondary3] [varchar](20) NULL,
[secondaryinten3] [int] NULL,
[secondary4] [varchar](20) NULL,
[secondaryinten4] [int] NULL)
empidprimary1primaryinten1secondary2secondaryinten2secondary3secondaryinten3secondary4secondaryinten4
1HF1NULLNULLNULLNULLNULLNULLNULLNULL
1NULLNULLCAD1NULLNULLNULLNULLNULLNULL
1NULLNULLCADO1NULLNULLNULLNULLNULLNULL
1NULLNULLMAN1NULLNULLNULLNULLNULLNULL
1NULLNULLMANt1NULLNULLNULLNULLNULLNULL
now i tried
--select * from emp_new
----drop table #emp_new1
CREATE TABLE #emp_new1(
[empid] [int] NULL,
[primary1] [varchar](20) NULL,
[primaryinten1] [int] NULL,
[secondary2] [varchar](20) NULL,
[secondary3] [varchar](20) NULL,
[secondary4] [varchar](20) NULL,
) ON [PRIMARY]
insert into #emp_new1
select empid,primary1,primaryinten1,secondarys2,secondarys3,secondarys4
from
(
select empid,primary1,primaryinten1,secondary2,
'secondarys'+ cast(ROW_NUMBER() over( partition by empid order by empid) as varchar(10)) rn
from
emp_new
)
d
pivot
(
max(secondary2)
for rn in (secondarys1,secondarys2,secondarys3,secondarys4))
piv
select * from #emp_new1
result is :
empidprimary1primaryinten1secondary2secondary3secondary4
1NULLNULLCADCADOMAN
1HF1NULLNULLNULL
but i want
empidprimary1primaryinten1secondary2secondary3secondary4
1 HF 1 CAD CADO man
JUST IN 1 LINE,PLEASE HELP ME WITH LAST PART
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply