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)
----------------------------------------------------------------------
empid primary1 primaryinten1 primary2 primaryinten2 primary3 primaryinten3 primary4 primaryinten4 primary5 primaryinten5 primary6 primaryinten6 primary7 primaryinten7
1 ws 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1 NULL NULL gh 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1 NULL NULL lg 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1 NULL NULL fd 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1 NULL NULL hj 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
----------------------------------------------------------------------------------------
whati want is everything in 1 line.
empid primary1 primaryinten1 primary2 primaryinten2 primary3 primaryinten3 primary4 primaryinten4 primary5 primaryinten5 primary6 primaryinten6 primary7 primaryinten7
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)
empid primary1 primaryinten1 secondary2 secondaryinten2 secondary3 secondaryinten3 secondary4 secondaryinten4
1 HF 1 NULL NULL NULL NULL NULL NULL NULL NULL
1 NULL NULL CAD 1 NULL NULL NULL NULL NULL NULL
1 NULL NULL CADO 1 NULL NULL NULL NULL NULL NULL
1 NULL NULL MAN 1 NULL NULL NULL NULL NULL NULL
1 NULL NULL MANt 1 NULL NULL NULL NULL NULL NULL
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 :
empid primary1 primaryinten1 secondary2 secondary3 secondary4
1 NULL NULL CAD CADO MAN
1 HF 1 NULL NULL NULL
but i want
empid primary1 primaryinten1 secondary2 secondary3 secondary4
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